Go to the Last Row for Counting Purposes
 

How do you go to the last row that contains data in a spreadsheet if
there are numerous rows that do not contain any data?

What I really want to do is to be able to determine the number of
"blocks" of data that I have. The "blocks" are separated by empty
rows. The problem is that the number of "blocks" will increase over
time as more data is added. I'm thinking that once I determine the
last row number of the last block, I could use that as a counter (and
divide by 14 since the spacing between blocks is 14)

Here is an example of the data that I'm working with:


xxxxxxxxxx
yyyyyyyyyy "block 1"
zzzzzzzzzz

14 rows

aaaaaaaaa
bbbbbbbbb "block 2"
cccccccccccc

14 rows

mmmmmmm
nnnnnnnnnn "block 3"
oooooooooo

14 rows

more data to be added in the future

Can you help? Thanks.


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

Go to the Last Row for Counting Purposes
 

Assuming column A can be used to make the determination:

Set rng = Columns(1).specialCells(xlConstants)
set lastBlock= rng.Areas(rng.Areas.count)
lastrow = LastBlock.Rows(LastBlock.rows.count).Row
msgbox rng.Areas.count & "Blocks, last row: " & LastRow

or

LastRow = cells(rows.Count,"A").End(xlup).Row
msgBox "Last Row is " & LastRow

--
Regards,
Tom Ogilvy


Novice wrote in message
...

How do you go to the last row that contains data in a spreadsheet if
there are numerous rows that do not contain any data?

What I really want to do is to be able to determine the number of
"blocks" of data that I have. The "blocks" are separated by empty
rows. The problem is that the number of "blocks" will increase over
time as more data is added. I'm thinking that once I determine the
last row number of the last block, I could use that as a counter (and
divide by 14 since the spacing between blocks is 14)

Here is an example of the data that I'm working with:


xxxxxxxxxx
yyyyyyyyyy "block 1"
zzzzzzzzzz

14 rows

aaaaaaaaa
bbbbbbbbb "block 2"
cccccccccccc

14 rows

mmmmmmm
nnnnnnnnnn "block 3"
oooooooooo

14 rows

more data to be added in the future

Can you help? Thanks.


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/