How to Find the Last Row with Excel VBA

Excel report automation involves writing VBA that can adapt with your data, finding the Last Row with Excel VBA is one of those tricks. 

Data ranges rarely stay the same over time and a useful bit of VBA code to learn is how you can use Excel to find the last row in a data range and then call it within the rest of your script which will save you having to alter your Excel VBA every time your Data Range changes.  Read on to find out how.

To find the last row in a data range using Excel VBA you can use a variety of methods.  Two useful methods to adopt are explained below along with some example code that you can copy and paste directly into your Excel VBA module:

Method 1 – Create a Variable

This method is quick and efficient and works well when you want to find the last row on a single worksheet within Excel VBA.

The first step is to define the last row, Excel needs to find where it is and then set that row number to a name so you can use it in future code without having to change the VBA yourself every time the range changes.

Below the a variable called LastRow is created and will always equal the last row of the active worksheet:

Sub LastRow()

'Find the last row of active worksheet in column 1 (aka col A)
LastRow = Cells(Rows.Count, 1).End(xlUp).Row

End Sub

Breaking the code down into plain English LastRow is now equal to the last row that contains data in column 1 of the active worksheet.

The last row is found by searching in column 1 from the bottom of the worksheet upwards, that way if you have any gaps in the data it will still find the true last row.

To base your last row count on a different column, for example column 3 (aka column C), then you just change the numeric value in the code from 1 to 3 like below:

Sub LastRow()

'Find the last row of active worksheet in column 3 (aka col C)
LastRow = Cells(Rows.Count, 3).End(xlUp).Row

End Sub

Calling the Lastrow variable in VBA

After setting the variable LastRow it can be used in place of the last row number in all places of your VBA code.

For example if you want to select the entire data range for columns A to C and you would code the following:

Sub LastRow()

'Select A1 to the last row of column C
Range(“A1:C” &LastRow).Select

End Sub

Method 2 – Create a Function

This method involves creating a custom function that can be called in the rest of VBA code.  It is useful when you have to work on multiple data-sets that span across several worksheets and the reason for this is after setting up the function you do not have to keep resetting the lastrow variable, it can be called from just a single statement.

The first step is to insert a new module in your VBA Editor and add the following code to create the LastRow function:

Function LastRow(sh As Worksheet) As Long
    Dim lastCell As Range
    On Error GoTo ErrorHandler
    Set lastCell = sh.Cells.Find(What:="*", _
        After:=sh.Range("A1"), _
        LookAt:=xlPart, _
        LookIn:=xlValues, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, _
        MatchCase:=False)
    If Not lastCell Is Nothing Then
        LastRow = lastCell.Row
    Else
        ' Handle the case where no data was found in column A
        LastRow = 1 ' or any default value you prefer
    End If
    Exit Function

ErrorHandler:
    ' Handle errors here, if needed
    LastRow = -1 ' or any error indicator you prefer
End Function

Save the module and then you have a custom function called LastRow that can be used anywhere in your code in place of the last row number. 

For example if you want to select the entire data range on the worksheet called Data for columns A to C and you would use the following::

Sub LastRow()

'Select A1 to the last row of worksheet "Data", column C
Range(“A1:C” &LastRow(Worksheets(“Data”))).Select

End Sub

Note that in this case you have to specify which worksheet the last row is calculated on but because it is called directly in the same line of code.  This does provide a benefit when you have to repeat the process of finding the last row of data ranges multiple times across different worksheets.

Summary

In conclusion, finding the last row in a data range using Excel VBA is a crucial skill for efficient data handling and automation. This guide has detailed two practical methods: creating a variable and creating a function.

These methods enable dynamic and adaptable VBA scripting, ensuring your Excel projects remain robust and flexible as data ranges evolve. Remember, understanding and implementing these techniques will greatly enhance your ability to manage and manipulate data in Excel, a skill that’s invaluable in many professional and personal Excel applications. Keep practicing these methods, and you’ll soon master the art of Excel VBA for finding the last row.

Keep Excelling,

Now that you’re equipped with the knowledge of finding the last row using Excel VBA, why not take your VBA skills further? Check out our related post on How to Prompt Users for a Folder Location in Excel VBA. This guide will walk you through creating user-friendly interfaces, making your Excel projects even more dynamic and interactive.