I am trying to do the following:
1. Count populated Rows using VB
2. If the # of populated rows is less than 2 post a message box dsiplaying
3. If the # of populated rows is greater than 3, continue on with the rest
of the macro.
Anything you can do to hlep is greatly appreciated.
This will look at the last populated cell in the desired row.
mc = 2 '"a"
x = Cells(Rows.Count, mc).End(xlUp).Row
If x < 2 Then
MsgBox "Only " & x
MsgBox "oh boy"
Microsoft MVP Excel
S...Refernce 2 sheets & same columns & rows
Is it possible to do the following...
A B C D A B
C D E F G H
2 3 5 5 4 7
1 1 1 1 1 1
80 A 61 2
8 1 # 61 3
6 B 61 4
4 2 62 5
Ok, Sheet 1 has alot of forumlas to get the #'s in Column A,...How do i enable all 1,00,000+ rows in excel 2007
Excel 2007 as more than 1,00,000 rows, but in our organisation setup i see
only 65536 rows. How do i enable the remaining.
Try to save as Excel 2007 *
"free_ur_mind" <email@example.com> wrote in message
> Excel 2007 as more than 1,00,000 rows, but in our organisation setup i see
> only 65536 rows. How do i enable the remaining.
.... and then close the file, and then reopen it.
You can change the default file format in Office button ...Group By Query
Hello. I need a little help. Is there a way to find out which records were
grouped in a group by query, and maybe flag that record with a specific tag
for example in this scenario:
ID Amount Name
1 123 First
2 123 First
3 123 F-irst
4 345 Second
5 345 Second
6 345 Secon-d
If I put this table in a group by query and Sum the amounts, I take the
LastOfName and LastOfID then I get something like this:
ID Amount Name
3 369 F-irst
6 ...Rows in an Excel worksheet
What limits the size of an Excel worksheet
Why 65537 columns and 256 Rows?
By how many bits it takes to store the number of rows or columns.
256 is 2^8 (one byte)
65536 is 256 x 256 or 2^16 (two bytes)
To change this would take a redesign of Excel and almost
certainly much bigger files. So it is a "Specification Limit"
and you will find "specification limits" if you look in HELP.-
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvp...Convert Rows data
I have the following sample data:
PARTNUMBER QTY RATe PRUN VALID_FROM_VALID_TO
ZWR1 0.1095 USD 1 20050512 20050526
XREZ12 0.1095 USD 1 20050527 99991231
is it possible to put this data in the following:
20050512 20050526 20050527 99991231
So basically make the exis...Moveing rows
I have to move a block of rows in order to have the spreadsheet in the order
I did a move and paste to move the rows and the existing rows ware
How do I move rows and not have them overwrite existing rows?
If you do ctrl + x and then select where you want it, then right click and
select insert cut cells
or copy and do it, then after you are sure you got it right delete the
Excel 95 - Excel 2007
Northwest Excel Solutions
"It is a good thing to follow the first law of holes;
if you...Printing hidden rows or columns
Is it possible to print hidden columns? My users have a column they do not
want to see on the screen but want it to print out for hand writing in the
column as there are not computers, laptops, or PDAs available at this time.
Thanks for your time.
The only way I know to do this is to have a macro run when you want to
print. The macro would unhide the column, print the range, hide the column
again. All this can be done in the background so the user would never see
that column. Post back if you want to pursue this. HTH Otto
"ramona" <firstname.lastname@example.org...Minimizing Rows of with similar data
I am using Excel 2007.
I have 3 columns A, B, and C such as below:
TU10-10 TU10 Tungsten Satin Wedding Band
TU10-10.5 TU10 Tungsten Satin Wedding Band
TU10-11 TU10 Tungsten Satin Wedding Band
TU10-11.5 TU10 Tungsten Satin Wedding Band
TU10-12 TU10 Tungsten Satin Wedding Band
TU10-12.5 TU10 Tungsten Satin Wedding Band
TU10-13 TU10 Tungsten Satin Wedding Band
TU10-6 TU10 Tungsten Satin Wedding Band
TU10-6.5 TU10 Tungsten Satin Wedding Band
TU10-7 TU10 Tungsten Satin Wedding Band
TU10-7.5 TU10 Tungsten Satin Wedding Band
TU10-8 TU10 Tungsten Satin Wedding Band
TU10-8.5 TU10 Tung...Code to delete rows excluding Header if not = to
I have the following code to delete all rows that do not contain the
following two variables, in column P, "cw" and "ea".
Set ws = ActiveSheet
For p = ws.Range("P65536").End(xlUp).Row To 1 Step -1
If ws.Cells(p, 16) <> "cw" Then
If ws.Cells(p, 16) <> "ea" Then
It works very well apart from the fact that it deletes the Header row.
Please can someone assist me in modifying this code so it does not delete Row
1 (the header row).
T...how do i increase the rows in a excel balance sheet?
i've prepare a balance sheet for my company.In the excel balance sheet i
can't enter more then 12 rows. what can i do/
"dass" <email@example.com> wrote in message
> i've prepare a balance sheet for my company.In the excel balance sheet i
> can't enter more then 12 rows. what can i do/
Could you be a bit more specific? Excel (at least in 2003 and prior) has
over 60 THOUSAND rows. What EXACTLY do you mean by "can't enter more than 12
Excel 95 and earlier h...Selecting alternate rows
I have a large file (Excel XP) with about 300+names and related details. I
need to break it into 2 files but I need to select alternate rows for each
file. It may come out to every 3rd row to work into 3 files.
Is there a quick way to do this rather than tediously select each alternate
row, copy to the new file and delete from the old file?
Many thanks for your help.
Many thanks - I shall give it a go. If I have a problem, I'll get back to you.
"B. R.Ramachandran" wrote:
> You can break your data into two worksheets as follows:
>...collapsing rows in Excel
I exported a spreadsheet from a SmartList that I created in SmartList
Builder. The SmartList is similar to the defaul Financial Transaction object
in SmartList that shows open year journal entries from subledgers such as
payables and receivables.
The spreadsheet contains the column called "Series". The rows in this
column are of course designated by such phrases as "Sales", "Purchasing", and
For "Sales" rows, I want to have Excel automatically remove data from three
columns and "collapse" the data in t...Graph to only include rows with data in
Hello, I want to make a graph which is based on a table which gets its data
from other parts of my spreadsheet - so all the data is made from formulas.
Some of the rows at the bottom will be empty, and I want the graph to ignore
these empty rows, but to include them if data is added at a later date.
Is there a way to point the graph to an area, but to tell the graph to
ignore any rows where the formulas return a blank (or Icould make it zeros
if that helps). I don't want acres of graph with no columns in either, so it
would have to adjust automatically.
I'm OKish with VBA, but...average on rows
can we use average in rows even two rows at the time when number are not in
consequent collums meaning there is some spaces between them without having
to add each number manually in the function window.
XP home , Office 97
any or all responses are gratefully accepted and thank you for your time
Have you tried? Your description is confusing to me, but AVERAGE()
ignores blank cells and text...
In article <C2D622EE-D839-4469-9DA9-F264B074BB6B@microsoft.com>,
justamailman <firstname.lastname@example.org> wrote:
> can we use average in rows even two rows at the...sorting data with two rows as column headings that reamian as headers
How do stop excell from automatically selecting the two top rows that contain headdings, when I use the sort comand on the data menu. This was not a problem until I allowed workbook sharing. Now I have to select the data manually to exclude the two header rows otherwise the headings are sorted to other rows which I do not want
When you access the Sort menu look to see if the "My list
has" and Header Row is selected.
>How do stop excell from automatically selecting the two
top rows that contain headdings, when I use the...Deleting blank rows with formulas in them
I have a spreadsheet that takes data from one worksheet and moves it to
several worksheets, depending on a certain value. There are several
worksheets where the data has been moved, based on a macro and formula I
There are several rows that will always be blank, however a formula appears
in those rows. Is there a way to create a macro that will look at all the
"named" sheets individually and find any rows from 8-300 that have a formula
in them, but do not contain actual data.
For instance, worksheet NEO has formulas that feed off another sheet from
row 8-300. H...HELP! Split cell contents to new rows
I know how to split cell contents to columns, but how do you tell excel to
take the cell contents and insert into new rows. Each name is separated by a
comma and I need each name to appear in its own row beneath that cell.
Copy, paste special-transpose (now in columns). Data - Text to columns.
Copy, paste special- transpose back to rows.
"nyarce" <email@example.com> wrote in message
>I know how to split cell contents to columns, but how do yo...Inserting rows #5
I have a spreadsheet that is approx. 200 rows long and I need to insert a new
row after each existing row. Please help!
Thanks for your time!
ASAP Utilities has a feature that does this nicely...........ASAP Utilities
Excel add-in, and is available free at www.ASAP-utilities.com
Vaya con Dios,
"Linda T" <Linda T@discussions.microsoft.com> wrote in message
> I have a spreadsheet that is approx. 200 rows long and I need to insert a
> row after each existing row. Please help!
>...Need rows in Column A removed if they fully or partially match with any Column B row
I have rows of text strings in Column A and in Column B.
I need rows in Column A removed if they partially or exactly
match with text strings in any Column B row.
Your input is appreciated!
You may get better, er, some responses if you describe what "partially match"
> Hi There,
> I have rows of text strings in Column A and in Column B.
> I need rows in Column A removed if they partially or exactly
> match with text strings in any Column B row.
> Your input is appreci...Number of rows and columns
I have a spreadsheet that has only 85 rows and columns to AH. After
that the spreadsheet is gray. I checked, but there are no hidden rows
and the VBA code does not have anything apparent to hide or limit the
rows/columns. Could somebody explain how that is done?
Thanks in advance.
You are in page break preview, click on the view drop down and select
normal to see the whole speardsheet
> I have a spreadsheet that has only 85 rows and columns to AH. After
> that the spreadsheet is gray. I checked, but there are no hidden rows
> and the...Using a formula to determine variable rows in Chart
I am trying to build a standard sheet with a chart. This chart may contain
28, 29, 30 or 31 rows depending upon the number of days in the month the
chart is prepared. A database application loads the data into Sheet1, and
sheet two contains the graph. The graph always shows 31 days. Sheet1
contains a cell stating the number of days to chart but I am, so far,
unsuccessful in using a formula or expression when stating the series range
in the chart data properties. It seems the only expression the chart
understands is a static range of cells. Because of security restrictions on
desk...How do I put a large (200 plus rows) table into Publisher
Have a large excel membership roster (200+) and have tried to put it into MS
Publisher 2003 as a landscape table. I've parsed the list into segments less
than 112 rows, but when it pastes it exceeds the page size 8.5 x11, and does
not flow like a text box.
Is there any way to do this, or will I have to reconstruct the 70 page
document into a MS Word document?
...Importing more than 65359 rows of data
I know there is a way to make excel accept more data. I have done it o
other computers but have forgotten the command. Can someone pleas
remind me how.
Message posted from http://www.ExcelForum.com
Use multiple worksheets????
"jgorski <" wrote:
> I know there is a way to make excel accept more data. I have done it on
> other computers but have forgotten the command. Can someone please
> remind me how.
> Message posted from http://www.ExcelForum.com/
Hi ...how do I keep my rows together?
I have two workbooks that I use to store different pieces
of information on the same employees. In both workbooks,
the first column is the employees' first name and the
second column is the employees' second name. After that,
the columns contain different pieces of information about
the employees. I need to make sure that if I add or
subtract an employee from one of the workbooks, he/she is
added/removed from both of them, so I tried to make the
first two columns in one workbook equal to the first two
columns in the other. The problem is that while the
cells in the first ...