Finding Last Row Column

The only way to accurately determine the next (or last row) that contains data is to check every cell using ActiveCell.Offset(1,0).
There are a number of alternatives, but most of them rely on the "UsedRange" property being updated.
Deleting rows or columns (or content that should impact the UsedRange) will not be reflected unless the UsedRange is reset.


Using Ctrl + End

Moves the selection from the active cell to the last used cell on the worksheet.
Cells that have been formatted (but contain no values) are considered "used".
This relies on the "UsedRange" property being reset.


Using UsedRange

One of the most common ways to find the last cell on a worksheet is to use the UsedRange property.
The "UsedRange" property can be reset either by saving the workbook or running code that uses the "UsedRange" property.

Dim llastrow As Long 
Dim llastcolumn As Long

llastrow = ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count - 1
Debug.Print llastrow

llastcolumn = ActiveSheet.UsedRange.Column + ActiveSheet.UsedRange.Columns.Count - 1
Debug.Print llastcolumn

When you have a "clean" worksheet the following code returns the correct last cell "E4".

However this property does not just include cells that contain actual data but also any cells that have been formatted.
For example if we run the same code on the following worksheet the last cell is "F5".


Using SpecialCells(xlCellTypeLastCell)

This is the equivalent of using Ctrl+End and therefore also relies on the "UsedRange" property.
The same is true for the xlCellTypeLastCell argument which can be used with the SpecialCells property.

llastrow = Range("A1").SpecialCells(XlCellType.xlCellTypeLastCell).Row 
Debug.Print llastrow

llastcolumn = Range("A1").SpecialCells(XlCellType.xlCellTypeLastCell).Column
Debug.Print llastcolumn

This also includes formatting in the range that is returned.
The xlLastCell constant is not documented because this constant was replaced in Excel 97 with the new xlCellType.xlCellTypeLastCell.
The following line of code does work but has only been included for backwards compatibility reasons.

llastrow = Range("A1").SpecialCells(xlLastCell).Row 

The macro recorder will generate code with the xlLastCell constant which adds to the confusion.


Using Cells.Find

Another popular approach which can be used to obtain the last "populated" row and column is to use the Cells.Find method.
This method searches backwards first by row and then by column from the top left cell.
The last row is the last visible row taking Filtering into account, but is not impacted by Manually Hidden Rows or collapsed Grouping.
The last column is the last visible column taking Manually Hidden Columns into account, but is not impacted by collapsed Grouping.

llastrow = ActiveSheet.Cells.Find(What:="*", _ 
                                  After:=ActiveSheet.Range("A1"), _
                                  SearchOrder:=XlSearchOrder.xlByRows, _
                                  SearchDirection:=XlSearchDirection.xlPrevious).Row
Debug.Print llastrow

Dim oRange As Range
Set oRange = Range("A1:E200")
llastrow = oRange.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Debug.Print llastrow

llastcolumn = ActiveSheet.Cells.Find(What:="*", _
                                     After:=ActiveSheet.Range("A1"), _
                                     SearchOrder:=XlSearchOrder.xlByColumns, _
                                     SearchDirection:=XlSearchDirection.xlPrevious).Column
Debug.Print llastcolumn

llastcolumn = oRange.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
Debug.Print llastcolumn

Using End(xlUp)

Use the following to obtain the address of the last non-empty cell in column "A".
The last row is the last visible row taking into account Filtering, Manually Hidden Rows and collapsed Grouping.

llastrow = Range(Range("A65536").End(xlDirection.xlUp).Address).Row 
llastrow = Range("A" & Rows.Count).End(xlUp).Row
Debug.Print llastrow

© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext