Introduction

Do you need to know what the last cell in an Excel row is? In VBA there's a macro for that. In this article that's exactly what we'll be discussing and showing demos of. Without further ado, let's jump into how to get the VBA Excel last row.

The VBA Excel Last Row

In an ordinary excel worksheet we use CTRL + Arrow Down Key as our shortcut key to go to the last row of a column. With VBA or Visual Basic for Applications we have macro code that will give us the cell number of the last row.

Finding the Last Row Using Cells Property

Let's take a look at an example:

Sub example1()

    Dim AB As Long
    AB = Cells(Rows.Count, 1).End(xlUp).Row

    MsgBox AB

End Sub

Code definition:

• Dim AB As Long - we have set our Dim variable AB to the Long datatype.
• AB = Cells(Rows.Count, 1) - To find the last row you need to count how many rows there are in the first column. We have stored this in our variable AB.
• .End(xlUp).Row - this will be the indicator to VBA as the the end key and up i.e.
• MsgBox AB - this part of the code is used to show us the result in a message box.

Let's take a look at the result after running the code:

Let's go over another example:

By using the code below, even if we have unused rows, the code will also detect those. The macro code will still give us a return of 15 in our message box.

Code:

Sub example1()

    Dim AB As Long
    AB = Cells(Rows.Count, 1).End(xlUp).Row

    MsgBox AB

End Sub

See the below excel worksheet reference and the result below:

Finding the Last Row using Range Object

We can also use the code below using Range:

Code:

Sub example2()

    Dim AB As Long
    AB = Cells(Rows.Count, 1).End(xlUp).Row

    Range("B2").Value = WorksheetFunction.Sum(Range("C2:C5" & AB))

    MsgBox AB

End Sub

Code definition:

• Range("B2") - this define that the method we are going to use is the range method.
• Value = WorksheetFunction.Sum(Range("C2:C5" & AB)) - this is our range value.

Let's take a look at the result below:

Finding the Last Row Using Both Cell Property and Range Object

Let's take a look at yet another way of doing it:

Sub example3()

       Dim AB As Long
       AB = Range("B:B").SpecialCells(xlCellTypeLastCell).Row

       MsgBox AB

End Sub

Code definition:

• AB = Range("B:B"). - this defines as our cell property value.
• SpecialCells(xlCellTypeLastCell).Row - this will indicate VBA that the action

The result for the code:

Finding the Last Row Using Used Range

We can also use UsedRange to get the same result:

Sub example4()

    Dim AB As Long
    AB = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row

    MsgBox AB

End Sub

Code definition:

• AB = this will indicate VBA the value of AB
• ActiveSheet.UsedRange.Rows - this will indicate VBA that we are going to use the used range method
• (ActiveSheet.UsedRange.Rows.Count).Row - this defines as used range method will be used in finding the total count of rows in the worksheet.

Again you can see we obtain the same result.

Finding the value of the last row

We can also find the value of a cell in the last row. Let's take a look at how we'd do that:

    Public Sub example6()


    Dim AB As Long

    AB = Range("D" & Rows.Count).End(xlUp).Value

    MsgBox AB

    End Sub

Code explanation:

• AB = Range("D" & Rows.Count).End(xlUp).Value - this defines as the value or our variable AB.
• Range("D" & Rows.Count) - this is the range that we are going to work on.
• End(xlUp).Value - this defines as the movement of VBA

And the result after we run down the code:

Our message box gives us a return of 78 because 78 is the last value under column D.

Conclusion

We have shown you a variety of ways to get the last row in a column in Excel using VBA. We also showed you how to get the value of a cell in the last row. If you have any questions please let us know so we can clear up any confusion. Thanks for joining us for another tutorial on Excel and VBA.