detect last row of data

  • Thread starter Thread starter m&m
  • Start date Start date
M

m&m

Hi guys,

any Macro that can be written to detect the last row of data ?
please help
 
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
 
Thank you very much for your help indeed !!!

Jim Cone said:
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
Click to expand...
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Add a new row in Named Table (Table1) and add data from txtboxes into last empty cells based on header named of the Table1 0
Find last weekday of the month with vba for all months 3
Find last row through a macro 8
Paste in column B using Last row of either column B, C or D. 10
adding lines of code to a macro to delete rows 11
Code Snippet - Data Filter 8
Macro to automatically select the last row of data after refresh 10
How to get Last Key or Key-Combo Pressed (Not Event)? 0

Back
Top