Finding the last used row using VBA in Excel

Although the task to get the last row seems straight, there are quite a few considerations that pop up ones we start looking for a way to determine it. In this article an experimental approach is taken to determine the semantics and suitability of code fragments found on the web.

The test was done changing the active cell from A1, A2, ... F2 and each time rerun the procedure TestLastUsedRow below. The techniques tested are:

What do we require to find?

Using

Using Worksheet.UsedRange

UsedRange returns a Range object representing the area of a worksheet that is being used in a broad sense including data, formatting and other uses. Therefore, if you are only interested in the last row containing data you can only use UsedRange as a boundary of the range in which the last row will be found.

Sub TestLastUsedRow()
Dim ws As Worksheet
Dim rng As Range: Set rng = ActiveCell
Set ws = rng.Worksheet
Dim rngSpecialCellsLastCell As Range: Set rngSpecialCellsLastCell = rng.SpecialCells(Type:=xlCellTypeLastCell)
Dim rngCurrentRegionLastRow As Range: Set rngCurrentRegionLastRow = rng.CurrentRegion
Dim rngUsedRangeLastRow As Range: Set rngUsedRangeLastRow = ws.UsedRange
Dim rngColumnEndXlDown As Range: Set rngColumnEndXlDown = rng.End(xlDown)
Dim rngFindPrevious As Range
Set rngFindPrevious = ws.Cells.Find(What:="*", After:=ws.Range("a1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
Debug.Print "rngSpecialCellsLastCell: " & rngSpecialCellsLastCell.Address & " - " & rngSpecialCellsLastCell.Row
Debug.Print "rngCurrentRegionLastRow: " & rngCurrentRegionLastRow.Address & " - " & rngCurrentRegionLastRow.Rows.Count
Debug.Print "rngUsedRangeLastRow:     " & rngUsedRangeLastRow.Address & " - " & rngUsedRangeLastRow.Rows.Count
Debug.Print "rngColumnEndXlDown:      " & rngColumnEndXlDown.Address & " - " & rngColumnEndXlDown.Row
Debug.Print "rngFindPrevious:         " & rngFindPrevious.Address & " - " & rngFindPrevious.Row
End Sub

A1
rngSpecialCellsLastCell: $F$8 - 8
rngCurrentRegionLastRow: $A$1:$D$3 - 3
rngUsedRangeLastRow:     $B$2:$F$9 - 8
rngColumnEndXlDown:      $A$1048576 - 1048576
rngFindPrevious:         $F$8 - 8
A2
rngSpecialCellsLastCell: $F$8 - 8
rngCurrentRegionLastRow: $A$2:$D$3 - 2
rngUsedRangeLastRow:     $B$2:$F$9 - 8
rngColumnEndXlDown:      $A$1048576 - 1048576
rngFindPrevious:         $F$8 - 8
B2
rngSpecialCellsLastCell: $F$8 - 8
rngCurrentRegionLastRow: $B$2:$D$3 - 2
rngUsedRangeLastRow:     $B$2:$F$9 - 8
rngColumnEndXlDown:      $B$1048576 - 1048576
rngFindPrevious:         $F$8 - 8
C2
rngSpecialCellsLastCell: $F$8 - 8
rngCurrentRegionLastRow: $B$2:$D$3 - 2
rngUsedRangeLastRow:     $B$2:$F$9 - 8
rngColumnEndXlDown:      $C$3 - 3
rngFindPrevious:         $F$8 - 8
D2
rngSpecialCellsLastCell: $F$8 - 8
rngCurrentRegionLastRow: $B$2:$D$3 - 2
rngUsedRangeLastRow:     $B$2:$F$9 - 8
rngColumnEndXlDown:      $D$3 - 3
rngFindPrevious:         $F$8 - 8
F2
rngSpecialCellsLastCell: $F$9 - 9
rngCurrentRegionLastRow: $F$2:$F$8 - 7
rngUsedRangeLastRow:     $B$2:$F$9 - 8
rngColumnEndXlDown:      $F$8 - 8
rngFindPrevious:         $F$8 - 8