View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Jim Cone Jim Cone is offline
external usenet poster
 
Posts: 3,290
Default detect last row of data

You are asking one of the most frequently asked questions in this newsgroup.
A Google search will answer a lot of questions before you have to ask.

The following function usually works for me...

'=======================================
' GetBottomRow() Function
' Jim Cone did it
' Returns the number of the last worksheet row with data.
' If the sheet is blank it returns 0.
'=======================================
Function GetBottomRow(ByRef TheSheet as Worksheet) As Long
On Error GoTo NoRow
' Line below is optional. Also a check for hidden rows could be done instead.
If TheSheet.FilterMode Then TheSheet.ShowAllData
GetBottomRow = TheSheet.Cells.Find(what:="*", _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
Exit Function
NoRow:
GetBottomRow = 0
End Function
'=======================================
'You can call the function like this...
Sub FindLastRowWithData()
Dim lngRw As Long
lngRw = GetBottomRow(ActiveSheet)
MsgBox lngRw
End Sub
'=======================================

Regards,
Jim Cone
San Francisco, CA

"m&m" wrote in message
...
Hi guys,
any Macro that can be written to detect the last row of data ?
please help