MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Finding the bottom of a sheet in Macro

Posted by Bill Mahoney on February 13, 2002 3:16 PM

Hello, i am trying to find out how to find the last row containing text in a sheet using code. I would appreciate any help.

Bill Mahoney
The Alcott Group

Posted by Barrie Davidson on February 13, 2002 3:32 PM

Bill, try something like:


BarrieBarrie Davidson

Posted by Jim Cone on February 13, 2002 3:33 PM


You can use the following function.

'Returns the number of the last worksheet row with data. It returns 0 if the sheet is blank.
'=================================================Function GetBottomRow() As Long
On Error GoTo NoRow
GetBottomRow = Cells.Find(what:="*", SearchOrder:=xlByRows, _
Exit Function
GetBottomRow = 0
End Function

You can call it like this:

Dim LastRow as Long
LastRow = GetBottomRow


Jim Cone
San Jose, CA


Posted by Derek on February 13, 2002 6:22 PM

Hi Bill
To check all columns to find the lowest row used I prefer this: (as usual, it will include any cell containing just a space)

Count = 0
For Each Cell In [A65536:IV65536]
If Cell.End(xlUp).Row > Count Then
Count = Cell.End(xlUp).Row
End If
Next Cell
MsgBox "" & Count
End Sub


Posted by Bill Mahoney on February 14, 2002 6:22 AM

Thanks for the help.