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:

Data set to find the last row in Excel

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:

Message box that shows the last row in the Dataset

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.

Runtime error when finding the last row

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:

ArgumentRequired/OptionalData typeDescription
TypeRequiredXlCellTypeThe cells to be included.
ValueOptionalVariantIf 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:

ConstantValueDescription
xlCellTypeSameValidation-4175Cells with the same validation criteria.
xlCellTypeAllValidation-4174Cells with validation criteria.
xlCellTypeSameFormatConditions-4173Cells with the same format.
xlCellTypeAllFormatConditions-4172Cells of any format.
xlCellTypeComments-4144Cells with notes.
xlCellTypeFormulas-4123Cells with formulas.
xlCellTypeConstants2Cells with constants.
xlCellTypeBlanks4Empty cells.
xlCellTypeLastCell11The last cell in the used range.
xlCellTypeVisible12All visible cells.

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

Data set to find the last row in Excel

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:

Message box showing the last row in the Dataset

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:

ArgumentDescription
WhatThe data to search for.
AfterThe 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.
LookInIt can be one of the following constants: xlValues, xlFormulas, xlComments, or xlCommentsThreaded.
LookAtIt can be one of the following constants: xlPart or xlWhole.
SearchOrderIt can be one of the following constants: xlByColumns or xlByRows.
SearchDirectionIt can be one of the following constants: xlPrevious or xlNext.
MatchCaseThe default value is ‘False’ to make the search case-insensitive. Use ‘True’ to make the search case-sensitive.
MatchByteIt 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.
SearchFormatThe format of the range, e.g., bold text.

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

Data set to find the last row in Excel

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.

Message box showing the last row in the Dataset3

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:

ConstantValueDescription
xlDown-4121Down.
xlToLeft-4159To Left
xlToRight-4161To Right.
xlUp-4162Up.

Find Last Row Using Using End(xlUp)

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

Data set to find the last row in Excel

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:

Message box showing the last row in the Dataset4

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:

Data set to find the last row in Excel

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.

Message box showing the last row in the Dataset5

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.

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:

Data set to find the last row in Excel

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.

Message box showing the last row in the Dataset6

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.

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:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.