Friday, November 26, 2010

Excel VBA: How to Find Out the Last Cell of a Worksheet

A simple and quick way of getting the real last used cell in a worksheet.

If an error occurs the LastCell range remains uninitialized and this can be confirmed from the parent subroutine or function.

Function LastCell(sSheet As String) As Range

    Dim LastRow As Long
    Dim LastRow LastCol As Long

    On Error Resume Next

    With Worksheets(sSheet)

        LastRow = .Cells.Find(What:="*", _
                              SearchDirection:=xlPrevious, _

        LastCol = .Cells.Find(What:="*", _
                              SearchDirection:=xlPrevious, _
        Set LastCell = .Cells(LastRow, LastCol)

    End With

End Function