Go to the Last Row for Counting Purposes

  • Thread starter Thread starter Novice
  • Start date Start date
N

Novice

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.
 
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
 
Back
Top