Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
detect last row of data
Hi guys,
any Macro that can be written to detect the last row of data ? please help |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
detect last row of data
Thank you very much for your help indeed !!!
"Jim Cone" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
need to detect syntax in value | Excel Discussion (Misc queries) | |||
How to detect #N/A and return 0? | Excel Discussion (Misc queries) | |||
How do I detect hidden worksheets or hidden data on a worksheet? | Excel Discussion (Misc queries) | |||
how to detect 5th character? | Excel Discussion (Misc queries) | |||
detect worksheet | Excel Programming |