Find Last Row, Column or Cell


Last Row or Column on WorkSheet


The macro 's below give you the last row or column number of the worksheet.
Note: The macros use the functions LastRow and LastCol below the macro
Tip: You can also use the sheet name instead of ActiveSheet like : Lr = LastRow(Sheets("YourSheet"))

Sub GetLastRowWithData()
Dim Lr As Long
Lr = LastRow(ActiveSheet)
MsgBox Lr
End Sub

Sub GetLastColumnWithData()
Dim Lc As Long
Lc = LastCol(ActiveSheet)
MsgBox Lc
End Sub

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function

Function LastCol(sh As Worksheet)
On Error Resume Next
LastCol = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
End Function


Last used cell in one row or one column


The macro 's below give you the row or column number of the last cell with data in one row or one column.
Note: The code below will not work correct if the last row or column with data is hidden.

Sub LastRowInOneColumn()
'Give the last used row in a Column: Column A in this example
Dim LastRow As Long
With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
MsgBox LastRow
End Sub

Sub LastColumnInOneRow()
'Give the last used column in a Row: Row 1 in this example
Dim LastCol As Integer
With ActiveSheet
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
End With
MsgBox LastCol
End Sub


Last used cell row or column in a worksheet with xlCellTypeLastCell or UsedRange

Possible problems with xlCellTypeLastCell and UsedRange are:
The xlCellTypeLastCell or UsedRange will only re-set when you save (or save/close/reopen the file).
If cell formatting is changed it will not reset the last cell, clearing the data is not enough, you must delete the rows or columns first. See: http://www.contextures.com/xlfaqApp.html#Unused

So when using VBA you cannot rely on the macros below.

Sub xlCellTypeLastCell_Example_Row()
Dim LastRow As Long
With ActiveSheet
LastRow = .Range("A1").SpecialCells(xlCellTypeLastCell).Row
End With
MsgBox LastRow
End Sub

Sub UsedRange_Example_Row()
Dim LastRow As Long
With ActiveSheet.UsedRange
LastRow = .Rows(.Rows.Count).Row
End With
MsgBox LastRow
End Sub


Sub xlCellTypeLastCell_Example_Column()
Dim LastColumn As Long
With ActiveSheet
LastColumn = .Range("A1").SpecialCells(xlCellTypeLastCell).Column
End With
MsgBox LastColumn
End Sub

Sub UsedRange_Example_Column()
Dim LastColumn As Long
With ActiveSheet.UsedRange
LastColumn = .Columns(.Columns.Count).Column
End With
MsgBox LastColumn
End Sub
7-March-2021
Web design by Will Woodgate