Find & Return The Last Used Cell On An Excel Worksheet Or Column

Got any Excel/VBA Questions? Excel Help

Finding the last used cell on Worksheet, or in Column, is a vital need in Excel VBA. The special cells method includes any cells that have had ANY default formatting changed, as does the UsedRange. Here is a custom function I use often. Copy paste into a Module and Run "Sub ExampleProcedure"

Dim rLastCell As Range


Sub ExampleProcedure()
    Run "LastUsedCell", 1
        If Not rLastCell Is Nothing Then
            MsgBox rLastCell.Address
        Else
            MsgBox "The Sheet is empty"
        End If
End Sub

Function LastUsedCell(lWsIndex As Long, Optional strColumn As String) As Range

'''''''''''''Written by www.ozgrid.com''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Returns a Range Object of the last used cell on Worksheet or in a Column.
'Sheet index number (lWsIndex) is mandatory and Column letter (strColumn) is optional.
'Example call from Procedure in the same Module.
''''''''''''''''''''' Run "LastUsedCell", 1, "A" '''''''''''''''''''''''''''''''''''''''
'IMPORTANT. WILL FAIL IF SHEET INDEX USED IS NOT A WORKSHEET.


    If strColumn = vbNullString Then
        With Sheets(lWsIndex).UsedRange
            Set rLastCell = .Find(What:="*", After:=.Cells(1, 1), LookIn:=xlFormulas, LookAt:= _
            xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False)
        End With
    Else
        With Sheets(lWsIndex)
            Set rLastCell = .Cells(.Rows.Count, strColumn).End(xlUp)
        End With
    End If
End Function