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, _
                              SearchOrder:=xlByRows).Row

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

    End With

End Function

2 comments:

  1. 1 of the 2 "Dim LastRow AS Long" entries should be "Dim LastCol AS Long"

    ReplyDelete
    Replies
    1. Yes it should, will correct. Thanks!

      Delete