VBA Finding the Last Populated Cell

Subscribe To
This Site

VBA Finding the Last Populated Cell, Row, or Column in a Worksheet.

Using VBA code for finding the last populated cell is simple. This can be accomplished by:

Sub Last_Populated_Cell()
    ActiveCell.SpecialCells(xlLastCell).Select
    LastR = ActiveCell.Row
    LastC = ActiveCell.Column
End Sub

Depending on what you are trying to accomplish, this cell may not be the cell that you are actually looking for. This is because, for example, if you type a value into cells A1,A2, and B1, Excel considers the last cell to be B2, which could have a value or not...

The so called problem arises when you delete all the values in the last row or column. In the example, if we delete both the values in cell A2 and B1, the above code will still show that the last row is 2 and the last column is 2. Thus, if you added a value to a cell and deleted it, it is considered used as far as this formula goes.

So, with VBA finding the last populated cell could look like this:

Sub Last_Real_Populated_Cell()
    ActiveCell.SpecialCells(xlLastCell).Select
    LastR = ActiveCell.Row
    LastC = ActiveCell.Column
    LastRealC = 1
    For Counter = LastR To 1 Step -1
        Range(Cells(Counter, LastC), Cells(Counter, LastC)).Select
        Selection.End(xlToLeft).Select
        If Not IsEmpty(ActiveCell.Value) Then
            LastRealR = ActiveCell.Row
            Exit For
        End If
    Next
    For Counter = LastC To 1 Step -1
        Range(Cells(LastR, Counter), Cells(LastR, Counter)).Select
        Selection.End(xlUp).Select
        If Not IsEmpty(ActiveCell.Value) Then
            LastRealC = ActiveCell.Column
            Exit For
        End If
    Next
    MsgBox "LastRealRow=" & LastRealR & vbCrLf & _
            "LastRealColumn=" & LastRealC & vbCrLf & _
            "Value=" & Cells(LastRealR, LastRealC).Value
End Sub

Note that what Excel calls the last populated cell may not actually be populated.

With VBA finding the last populated row with a value could be done like this:

Sub Last_Real_Populated_Row()
    ActiveCell.SpecialCells(xlLastCell).Select
    LastR = ActiveCell.Row
    LastC = ActiveCell.Column
    LastRealC = 1
    For Counter = LastR To 1 Step -1
        Range(Cells(Counter, LastC), Cells(Counter, LastC)).Select
        Selection.End(xlToLeft).Select
        If Not IsEmpty(ActiveCell.Value) Then
            LastRealR = ActiveCell.Row
            Exit For
        End If
    Next
End Sub

With VBA finding the last populated Column with a value could be done like this:

Sub Last_Real_Populated_Column()
    ActiveCell.SpecialCells(xlLastCell).Select
    LastR = ActiveCell.Row
    LastC = ActiveCell.Column
    LastRealC = 1
    For Counter = LastC To 1 Step -1
        Range(Cells(LastR, Counter), Cells(LastR, Counter)).Select
        Selection.End(xlUp).Select
        If Not IsEmpty(ActiveCell.Value) Then
            LastRealC = ActiveCell.Column
            Exit For
        End If
    Next
End Sub

Return from vba finding the last populated to "VBA Code Samples"

Return to our "Home Page"