Find Last Row Using VBA

When working with Excel, you may need to determine the last row of a dataset to, for example, identify where you can append new data.

In this tutorial, I will show you some easy ways of finding the last row of a dataset using VBA.

Find the Last Row Using the Range.CurrentRegion Property

The ‘Range.CurrentRegion’ property produces a range object representing the current region. The current region is a block of cells enclosed by any combination of blank columns and blank rows.

The syntax of the property:

expression.CurrentRegion

expression is a variable standing for a range object.

Suppose you have the following dataset on Sheet1 showing the average gas prices in various states:

You can use the VBA code below to ascertain the dataset’s last row:

Sub FindLastRowCurrentRegion()
Dim lastRow As Long
lastRow = Sheets("Sheet1").Range("A1").CurrentRegion.Rows.Count
MsgBox "The last row of dataset on Sheet1 is: " & lastRow
End Sub

When you execute the code, it identifies the dataset’s last row and displays a message box indicating the row number:

Explanation of the Code

This VBA code determines the last row with data in column A of the current region in ‘Sheet1’ and shows a message box giving us the row number that contains the last filled row.

The advantage of this method is that the code dynamically adapts to changes in the dataset.

Note: You cannot use this method on a  protected sheet. If you attempt this method on a protected sheet, you will get a runtime error 1004.

You must unprotect the worksheet to use this method.

Also read: Count Rows using VBA in Excel

Find the Last Row Using the Range.SpecialCells Method

The ‘Range.SpecialCells’ method produces a range object representing all the cells matching the specified type and value.

The syntax of the method:

expression.SpecialCells (Type, Value)

expression is a variable standing for a range object.

The following are the arguments or parameters of the method:

Argument	Required/Optional	Data type	Description
Type	Required	XlCellType	The cells to be included.
Value	Optional	Variant	If the ‘Type’ argument is either ‘xlCellTypeFormulas’ or ‘xlCellConstants,’ this argument determines which types of cells to include in the result. You can add these values to return multiple types. The default is to select all formulas or constants, regardless of type.

The following table shows the constants that you can use for the ‘XlCellType’ data type:

Constant	Value	Description
xlCellTypeSameValidation	-4175	Cells with the same validation criteria.
xlCellTypeAllValidation	-4174	Cells with validation criteria.
xlCellTypeSameFormatConditions	-4173	Cells with the same format.
xlCellTypeAllFormatConditions	-4172	Cells of any format.
xlCellTypeComments	-4144	Cells with notes.
xlCellTypeFormulas	-4123	Cells with formulas.
xlCellTypeConstants	2	Cells with constants.
xlCellTypeBlanks	4	Empty cells.
xlCellTypeLastCell	11	The last cell in the used range.
xlCellTypeVisible	12	All visible cells.

Suppose you have the following dataset on Sheet2 showing the average gas prices in various states:

You can use the VBA code below to ascertain the dataset’s last row:

Sub FindLastRowSpecialCells()
Dim LastRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet2")
LastRow = ws.Range("A:A").SpecialCells(xlCellTypeLastCell).Row
MsgBox "The last row of dataset on Sheet2 is: " & LastRow
End Sub

When you run the subroutine, it finds the dataset’s last row displays a message box indicating the row number:

Explanation of the Code

This VBA code finds and displays the last row with data or formatting in column A of “Sheet2” in an Excel workbook.

It uses the SpecialCells method with the xlCellTypeLastCell argument to identify the very last cell in column A that has been used, whether it contains data or has been formatted.

After determining this last cell’s row number, it presents this information to the user through a message box, stating the last row’s number in the dataset or formatted range on “Sheet2”.

Also read: Delete Blank Rows Using VBA

Find the Last Row Using the Range.Find Method

The ‘Range.Find’ method returns a range object representing the first cell where the information searched is found.

The method’s syntax:

expression.Find (What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)

expression is a variable standing for a range object.

Note: All the parameters are of the Variant data type and are all optional except the ‘What’ parameter, which is required.

The following table summarizes the method’s arguments or parameters:

Argument	Description
What	The data to search for.
After	The cell after which you want the search to start. Notice that After must be a single cell in the range. The search starts after this cell and is only searched once the code returns to this cell.If you omit this argument, the search starts after the cell in the upper-left corner of the range.
LookIn	It can be one of the following constants: xlValues, xlFormulas, xlComments, or xlCommentsThreaded.
LookAt	It can be one of the following constants: xlPart or xlWhole.
SearchOrder	It can be one of the following constants: xlByColumns or xlByRows.
SearchDirection	It can be one of the following constants: xlPrevious or xlNext.
MatchCase	The default value is ‘False’ to make the search case-insensitive. Use ‘True’ to make the search case-sensitive.
MatchByte	It is only applicable if you have selected or installed double-byte language support. Set ‘False’ to have double-byte characters match their single-byte equivalents. Use ‘True’ to have double-byte characters match only double-byte characters.
SearchFormat	The format of the range, e.g., bold text.

Assume you have the following dataset on Sheet3 showing the average gas prices in various states:

You can use the VBA code below to ascertain the dataset’s last row:

Sub FindLastRow()
Dim lastRow As Long
Dim Rng As Range
    Set Rng = Sheets("Sheet3").Columns("A").Find("*", , xlValues, xlWhole, xlByRows, xlPrevious)
    If Not Rng Is Nothing Then
        lastRow = Rng.Row
    End If
MsgBox "The last row of dataset on Sheet3 is: " & lastRow
End Sub

When you execute the subroutine, it identifies the last row and displays a message box showing the row number.

Explanation of the Code

This VBA code identifies the last row used in column A of “Sheet3” in an Excel workbook by searching for any cell with content (“*”) from the bottom up.

It uses the Find method to look through column A for the last cell that contains any value, treating the search in terms of values (xlValues), matching the whole cell content (xlWhole), by rows (xlByRows), and starting from the bottom (xlPrevious).

If such a cell is found, its row number is assigned to lastRow.

Finally, it displays a message box to the user indicating the row number of the last cell with data in column A on “Sheet3”.

This approach is effective for locating the last used row in a specific column, even if there are gaps or empty cells above it.

Also read: VBA to Freeze Top Row in Excel

Find Last Row Using the Range.End Property

The ‘Range.End’ property returns a range object representing the cell at the end of the region containing the source range. Applying the ‘Range.End’ property is equivalent to pressing End+Down Arrow, End+Up Arrow, End+Right Arrow , or End+Left Arrow.

The syntax of the property:

expression.End (Direction)

expression is a variable representing a range object.

The ‘Range.End’ property requires the ‘Direction’ argument of the ‘XlDirection’ data type specifying the direction to move.

‘XlDirection’ can have the following constants:

Constant	Value	Description
xlDown	-4121	Down.
xlToLeft	-4159	To Left
xlToRight	-4161	To Right.
xlUp	-4162	Up.

Find Last Row Using Using End(xlUp)

Suppose you have a dataset on Sheet4 with average gas prices across some states:

You can use the VBA code below to ascertain the dataset’s last row:

Sub FindLastRowRangeEndXlUp()
Dim lastRow As Long
With Sheets("Sheet4")
    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
MsgBox "The last row of dataset on Sheet4 is: " & lastRow
End Sub

When you execute the code, it locates the last row and displays a message box indicating the row number:

Explanation of the Code

The code moves up from the last cell in column A until it encounters the first non-empty cell, effectively identifying the last used cell in column A.

The ‘Row’ property then retrieves the row number of the last used cell in column A, equivalent to the last row of the dataset on Sheet4.

One big downside of this method is that if the column you use to find the last row has empty cells at the bottom, the code gives misleading results.

Find Last Row Using Using End(xlDown)

Assume on Sheet5 you have a dataset of average gas prices across some states:

You can use the VBA code below to determine the dataset’s last row:

Sub FindLastRowRangeEndXlDown()
Dim lastRow As Long
lastRow = Sheets("Sheet5").Cells(1, "A").End(xlDown).Row
MsgBox "The last row of dataset on Sheet5 is: " & lastRow
End Sub

When you execute the subroutine, it identifies the last row and sends a message box to the screen indicating the number of the last row.

Explanation of the Code

This method uses the ‘Range.End’ property with ‘xlDown’ to find the last row by starting from the first cell in a specific column (A1, in this example) and moving downward until the code finds the last non-empty cell.

The ‘Row’ property then retrieves the row number of the last non-empty cell in column A, equivalent to the last row of the dataset on Sheet5.

The disadvantage of this method is that it produces incorrect results if the column you use in the code to find the last row contains empty cells.

Also read: VBA to Find Value in Range in EXCEL

Find Last Row Using the Worksheet.UsedRange Property

The ‘Worksheet.UsedRange’ property returns a range object representing the specified worksheet’s used range. The used range is the group of cells that have been used on a worksheet, including all cells that have ever been used, even if they are currently empty.

The syntax of the property:

expression.UsedRange

expression is a variable representing a worksheet object.

Assume you have the following dataset on Sheet6 showing the average gas prices in various states:

You can use the VBA code below to ascertain the dataset’s last row:

Sub FindLastRowUsedRange()
Dim lastRow As Long
lastRow = Sheets("Sheet6").UsedRange.Rows.Count
MsgBox "The last row of dataset on Sheet6 is: " & lastRow
End Sub

When you execute the subroutine, it identifies the dataset’s last row and displays a message box indicating the row number.

Explanation of the Code

This VBA code calculates the total number of rows within the Used Range of “Sheet6” in an Excel workbook and displays this number via a message box.

The UsedRange property identifies all cells that have ever been used in the worksheet, including those with formatting and data.

By counting the rows (Rows.Count) of this range, the code captures the total number of rows that are considered “used” on “Sheet6”

The downside of this method is that if you delete data, the used range still includes the cells used before deletion, leading to a situation where the used range is larger than the actual dataset. This situation results in incorrect results.

Also read: Autofill to Last Row Using VBA in Excel

Some Use Cases of Finding the Last Row Using VBA

The following are some use cases of finding the last row using VBA:

1. When you want to analyze or generate reports based on the data in a worksheet, finding the last row allows you to dynamically adapt to changes in the dataset.
2. Checking for the last row is helpful when validating data to ensure you cover the entire range of data in a column.
3. Before inserting new data, you should find the last row to determine where to add the latest data.
4. Before deleting or clearing data, knowing the last row is essential so you don’t inadvertently remove more data than intended.
5. Finding the last row allows you to iterate only over the actual data rather than the entire column when looping through rows.
6. Before copying and pasting data, you should find the last row to ensure you copy the correct range.

In this tutorial, I showed you five ways of finding the last row of a dataset using VBA. I hope you found the tutorial helpful.

Other Excel articles you may also like:

• What is VBA in Excel?
• VBA to Delete Rows Based On Cell Value
• VBA to Hide Rows Based On Cell Values
• How to Print Row Numbers in Excel?
• Select Range in Excel Using VBA
• VBA to Insert Row in Excel