excel-vba Find the Last Non-Empty Cell in a Column


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:

  1. for last used row of "Sheet1":
    LastRow = wS.UsedRange.Row - 1 + wS.UsedRange.Rows.Count.

  2. 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