excel-vba Methods for Finding the Last Used Row or Column in a Worksheet Find the Last Non-Empty Cell in a Column
Example
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
"Sheet1":LastRow = wS.UsedRange.Row - 1 + wS.UsedRange.Rows.Count. -
for last non-empty cell of Column
"A"in"Sheet1":Dim i As Long For i = LastRow To 1 Step -1 If Not (IsEmpty(Cells(i, 1))) Then Exit For Next i LastRow = i
