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