I've got a spreadsheet that's set up with (for example) 1000 rows (with
properties set for those 1000 rows). But likely only a few hundred of those
will actually contain data (the rest will be blank).

Is there an easy way (in VBA) to find which row has the last non-blank data?

At first I thought I'd use:

ActiveSheet.UsedRange.Rows.Count


Unfortunately, that seems to be returning my 1000 rows every time (even if
the "real" data is only up to row 200).

I'd rather not build a for...next loop to find it, as that'd be horrifically
slow.

Any ideas?


Thanks!
-Scott

Go to

http://groups.google.com/advanced_group_search

and search for

Last cell in range

Alan Beban


Scott Lyon wrote:
I've got a spreadsheet that's set up with (for example) 1000 rows (with
properties set for those 1000 rows). But likely only a few hundred of those
will actually contain data (the rest will be blank).

Is there an easy way (in VBA) to find which row has the last non-blank data?

At first I thought I'd use:

ActiveSheet.UsedRange.Rows.Count


Unfortunately, that seems to be returning my 1000 rows every time (even if
the "real" data is only up to row 200).

I'd rather not build a for...next loop to find it, as that'd be horrifically
slow.

Any ideas?


Thanks!
-Scott

That worked nicely! And quick too!

Thanks, Chip!

-Scott

"Chip Pearson" wrote in message
...
Scott,

Try something like

Dim LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
MsgBox "Last Row Is " & LastRow

Change the "A" to the appropriate column.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Scott Lyon" <scott.lyonNOSPAM_at__NOSPAMrapistan.comNOSPAM wrote
in message ...
I've got a spreadsheet that's set up with (for example) 1000
rows (with
properties set for those 1000 rows). But likely only a few
hundred of those
will actually contain data (the rest will be blank).

Is there an easy way (in VBA) to find which row has the last
non-blank data?

At first I thought I'd use:

ActiveSheet.UsedRange.Rows.Count


Unfortunately, that seems to be returning my 1000 rows every
time (even if
the "real" data is only up to row 200).

I'd rather not build a for...next loop to find it, as that'd be
horrifically
slow.

Any ideas?


Thanks!
-Scott