Find Last Row, Column or Cell

Last Row or Column on WorkSheet

The macro 's below give you the last row or column number of the worksheet.
Note: The macros use the functions LastRow and LastCol below the macro
Tip: You can also use the sheet name instead of ActiveSheet like : Lr = LastRow(Sheets("YourSheet"))


Sub GetLastRowWithData()
    Dim Lr As Long
    Lr = LastRow(ActiveSheet)
    MsgBox Lr
End Sub

Sub GetLastColumnWithData()
    Dim Lc As Long
    Lc = LastCol(ActiveSheet)
    MsgBox Lc
End Sub

Function LastRow(sh As Worksheet)
    On Error Resume Next
    LastRow = sh.Cells.Find(What:="*", _
                            After:=sh.Range("A1"), _
                            Lookat:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).Row
    On Error GoTo 0
End Function

Function LastCol(sh As Worksheet)
    On Error Resume Next
    LastCol = sh.Cells.Find(What:="*", _
                            After:=sh.Range("A1"), _
                            Lookat:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByColumns, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).Column
    On Error GoTo 0
End Function

Last used cell in one row or one column

The macro 's below give you the row or column number of the last cell with data in one row or one column.
Note: The code below will not work correct if the last row or column with data is hidden.


Sub LastRowInOneColumn()
'Give the last used row in a Column: Column A in this example
    Dim LastRow As Long
    With ActiveSheet
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
    MsgBox LastRow
End Sub

Sub LastColumnInOneRow()
'Give the last used column in a Row: Row 1 in this example
    Dim LastCol As Integer
    With ActiveSheet
        LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
    End With
    MsgBox LastCol
End Sub

Last used cell row or column in a worksheet with xlCellTypeLastCell or UsedRange

Possible problems with xlCellTypeLastCell and UsedRange are:
The xlCellTypeLastCell or UsedRange will only re-set when you save (or save/close/reopen the file).
If cell formatting is changed it will not reset the last cell, clearing the data is not enough, you must delete the rows or columns first. See: http://www.contextures.com/xlfaqApp.html#Unused

So when using VBA you cannot rely on the macros below.


Sub xlCellTypeLastCell_Example_Row()
    Dim LastRow As Long
    With ActiveSheet
        LastRow = .Range("A1").SpecialCells(xlCellTypeLastCell).Row
    End With
    MsgBox LastRow
End Sub

Sub UsedRange_Example_Row()
    Dim LastRow As Long
    With ActiveSheet.UsedRange
        LastRow = .Rows(.Rows.Count).Row
    End With
    MsgBox LastRow
End Sub


Sub xlCellTypeLastCell_Example_Column()
    Dim LastColumn As Long
    With ActiveSheet
        LastColumn = .Range("A1").SpecialCells(xlCellTypeLastCell).Column
    End With
    MsgBox LastColumn
End Sub

Sub UsedRange_Example_Column()
    Dim LastColumn As Long
    With ActiveSheet.UsedRange
        LastColumn = .Columns(.Columns.Count).Column
    End With
    MsgBox LastColumn
End Sub