Best way to find row number of last row containing data

  • Thread starter Thread starter Brian Pollard
  • Start date Start date
B

Brian Pollard

I would like to know the best way of find the row number of the last row
containg data in a worksheet.
Searching through newsgroups and web sites I found the first option (which
is commented out below)
but this is column specific i.e. it only works if the last used cell in the
worksheet is in column A.

My latest effort is the LROW line without the comment - but is this the best
or does anyone know a better or more efficient way?


Function LROW() As Long
' Returns the row number of the last row containing a non-blank entry in a
worksheet
Application.Volatile
' LROW = Range("a65536").End(xlUp).Row
LROW = Application.Caller.Parent.UsedRange.Rows.Count
End Function
 
I think it depends on what you're looking for and how you define last used row.

Ron de Bruin has lots of sample code with different interpretations:
http://www.rondebruin.nl/last.htm

Personally, if I know the data, I like to pick out a column that always has data
in it if that row is used and then use something like:

dim LastRow as long
with worksheets("SomeSheetNameHere")
lastrow = .cells(.rows.count,"X").end(xlup).row
end with

But you're right. If you don't know the data, you may not be able to pick out a
column that qualifies.

And because excel remembers the usedrange until you try to reset it, you may not
want to rely on somesheethere.cells.specialcells(xlcelltypelastcell).row.

Debra Dalgleish does share some techniques for resetting that usedrange:
http://contextures.com/xlfaqApp.html#Unused

And depending on what you're doing, you may find that comments and even
formatting are important. It really makes life easier if you know the data.
 
Back
Top