M
m&m
Hi guys,
any Macro that can be written to detect the last row of data ?
please help
any Macro that can be written to detect the last row of data ?
please help
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
J
Jim Cone
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
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&m
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