Find Last Cell, Column And Row in Excel Worksheet

Hi All,

Please find below a small code snippet, which you can use to find the last Used Cell, Row or Column.

Public Function LastUsedCell() As Range
    Dim rng As Range
    Dim activeSht As Worksheet
    On Error GoTo ErrHandler
    
    Set activeSht = activeSheet

    Set rng = activeSht.Range("A1").SpecialCells(xlCellTypeLastCell)
    
    'return value
    Set LastUsedCell = rng
    Exit Function
ErrHandler:
    Set LastUsedCell = Nothing
End Function

Public Function LastUsedRow() As Long
    Dim rng As Range
    Dim activeSht As Worksheet
    On Error GoTo ErrHandler
    
    Set activeSht = activeSheet

    Set rng = activeSht.Range("A1").SpecialCells(xlCellTypeLastCell)
    
    'return value
    LastUsedRow = rng.Row
    Exit Function
ErrHandler:
    LastUsedRow = 0

End Function


Public Function LastUsedColumn() As Integer
    Dim rng As Range
    Dim activeSht As Worksheet
    On Error GoTo ErrHandler
    
    Set activeSht = activeSheet

    Set rng = activeSht.Range("A1").SpecialCells(xlCellTypeLastCell)
    
    'return value
    LastUsedColumn = rng.Column
    Exit Function
ErrHandler:
    LastUsedColumn = 0

End Function

Hope this helps :)

Thanks,
Vikas