find last row of data

[Guest]

hi,
i was wondering if there is a function that returns the row number of the
last row of data in a file.
i usually just loop down a column until cell value is empty but now i have a
file that has empty cells in betweenm and this doesn't work.

thanx a lot

 

[Norman Jones]

Hi Delali,

Try:

Dim rw As Long
rw = Cells(Rows.Count, "A").End(xlUp).Row

 

[Alan Beban]

hi,
i was wondering if there is a function that returns the row number of the
last row of data in a file.
i usually just loop down a column until cell value is empty but now i have a
file that has empty cells in betweenm and this doesn't work.

thanx a lot

You could just continue to loop a la

For i = 65536 To 1 Step -1
If Range("A" & i).Value <> "" Then Exit For
Next
rw = i

Unlike Norman Jones's suggestion, this will work if the last row of
Column A has data.

Perhaps faster for the same functionality would be to use the Find
method, searching from Cell A1 backwards.

Alan Beban

 

[Tom Ogilvy]

Dim rw As Long
if not isempty(Range("A" & rows.count)) then
rw = rows.count
else
rw = Cells(Rows.Count, "A").End(xlUp).Row
End if

would also account for the last cell having data.