Count rows starting in particular row

**bosco2** · 04-27-2011, 08:37 AM

Hello all -

I am trying to COUNT the number of rows in a range of cells until I come to a blank line (the last two rows of my spreadsheet are a blank line and then a TOTALS line which I dont want to count). Is this possible?? For example start in A3 and count until you hit a blank line.

This is the code I havebeen working with:

Please Login or Register  to view this content.

The A3 and A10 just seem to designate the range of cells to count and not the actual starting point???
Any help would be great!!

**Richard Buttrey** · 04-27-2011, 08:49 AM

Hi,

It's not clear - at least to me what you'r after. You are defining the starting point as A3 (or A10) and hence the .Range("A3").CurrentRegion.Rows.Count will return the number of non blank contiguous rows starting in A3.

Incidentally I always prefer to put Totals rows above any data since this is always a fixed position and you don't need to worry about how many data rows there may be. Justspecify a sufficiently large number in the =SUM()

HTH

**bosco2** · 04-27-2011, 09:07 AM

It's not clear - at least to me what you'r after. You are defining the starting point as A3 (or A10) and hence the .Range("A3").CurrentRegion.Rows.Count will return the number of non blank contiguous rows starting in A3.

This is NOT correct although it is what I was hoping for. A3 (or A10) represent the "range or group" to be counted. For example if I have data in rows A3:A20...........I can put A3 or A4 or A5 or A6 ....etc and it will return the exact SAME count. This is NOT used as a starting point. Try it!

And this is a template spreadsheet that is sent to me each month......I do not have an option of changing where the totals are, etc.

**Richard Buttrey** · 04-27-2011, 11:15 AM

Hi

Yes of course it will return the exact same count. That's what the .CurrentRegion instruction does. I think you may be misunderstanding what CurrentRegion is used for. With a contiguous range and the active cell anywhere in the range you'll always get the same row count.

It sounds like you may want to use the .End(xldown) syntax. Something like

Please Login or Register  to view this content.

Regards

**bosco2** · 04-27-2011, 12:22 PM

That makes perfect sense regarding currentRegion and your example works great....Thank you!

One additional questions is can I set this up so I do not have to be on the Sheet in order to perform the count? For example can I run that command for multiple worksheets?

I tried adding:

Sheets(1).Range(Range("A2"), Range("A2").End(xlDown)).Rows.Count

But I get a Run Time error if I am not on Sheet(1) ???

**Richard Buttrey** · 04-27-2011, 01:33 PM

Hi,

The original code should do that since there is no reference to a sheet.

Incidentally and I know you don't want to restrict this to sheet 1 but your example code would not work for sheet1 anyway since you had not specified the sheet reference in the right place.

instead of

Please Login or Register  to view this content.

you should have used

Please Login or Register  to view this content.

..and another incidentally. Remember that there is a subtle difference between Sheets(1) and Sheet1.
The former references the first tab sheet name starting from the left. If the sheet name 'Sheet2' happens to be the first sheet that's the sheet that will be referenced with Sheets(1).
The latter is the reference to the VBA sheet code name and will always reference the sheet wherever it happens to be physically located.

HTH