In this example, we will look at a method for returning the last non-empty row in a column for a data set.
This method will work regardless of empty regions within the data set.
However caution should be used if merged cells are involved, as the
End method will be "stopped" against a merged region, returning the first cell of the merged region.
In addition non-empty cells in hidden rows will not be taken into account.
Sub FindingLastRow() Dim wS As Worksheet, LastRow As Long Set wS = ThisWorkbook.Worksheets("Sheet1") 'Here we look in Column A LastRow = wS.Cells(wS.Rows.Count, "A").End(xlUp).Row Debug.Print LastRow End Sub
To address the limitations indicated above, the line:
LastRow = wS.Cells(wS.Rows.Count, "A").End(xlUp).Row
may be replaced with:
for last used row of
LastRow = wS.UsedRange.Row - 1 + wS.UsedRange.Rows.Count.
for last non-empty cell of Column
Dim i As Long For i = LastRow To 1 Step -1 If Not (IsEmpty(Cells(i, 1))) Then Exit For Next i LastRow = i