papa jonah

[Papa Jonah]

I am trying to find a simple formula that I can use to return the row number
for the last entry on a sheet. In other words, if I have 987 rows of data, I
would like this formula (to be included in a cell or VBA) to return "987" as
the value.

TIA

 

[Gord Dibben]

Papa

If the data is numeric........

=MATCH(9.99999999999999E+307,A:A)

If the data is text...........

=LOOKUP(REPT("z",255),A:A)

VBA code to rturn the row number......

Sub FindLastRow()
Dim LastRow As Long
If WorksheetFunction.CountA(Cells) > 0 Then
LastRow = Cells.Find(What:="*", after:=[A1], _
SearchOrder:=xlByRows, _
searchdirection:=xlPrevious).Row
MsgBox LastRow
End If
End Sub

Gord Dibben Excel MVP

 

[Frank Kabel]

Hi
in adddition to the answer you have received see:
http://www.xldynamic.com/source/xld.LastValue.html