Finding the last used cell on Worksheet, or in Column, is a vital need in Excel VBA. The SpecialCells Method includes any cells that have had ANY default formatting changed, as does the UsedRange. Here is a custom function ( Custom Function ) I use often. Copy paste into a Module and Run Sub ExampleProcedure
Code
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