Excel VBA to Select Last Row

In Excel, if we need to add a new row after the last row in a large dataset, it can become tedious and time-consuming to go to the last row. Although we can use some keyboard shortcuts to reach the bottom row of a dataset, these do not consider the rows after a blank row.

In this article, we will demonstrate different Excel VBA code to select the last row of a dataset that contains values. We’ll use the dataset below containing information about some employees to illustrate our methods.

Sample Dataset


Method 1 – Using End(xlUp) Method

Here is the code:

Sub EndxlUp()
Dim ws As Worksheet
Dim lastRow As Long
Set ws = ThisWorkbook.Worksheets("EndxlUp")
lastRow = ws.Range("B" & Rows.Count).End(xlUp).Row
ws.Rows(lastRow).Select
End Sub

Excel VBA Code with End(xlUp) to Select Last Row

VBA Breakdown

Dim ws As Worksheet
 Dim lastRow As Long
 Set ws = ThisWorkbook.Worksheets("EndxlUp")
  • We declare “ws” as a Worksheet variable and “lastRow” as a Long variable. We set the value of ws to the worksheet “EndxlUp”.
 lastRow = ws.Range("B" & Rows.Count).End(xlUp).Row
  ws.Rows(lastRow).Select
  • We set the “lastRow” variable to the last used row in column B of the “EndxlUp” worksheet. The Count property returns the total number of rows in the worksheet, and End(xlUp) method returns the last non-empty cell in column B by searching from the bottom of the column towards the top. the Row property returns the row number of the last non-empty cell. In this case, it will be 14.
  • Finally, we select the entire 14th row with the .Select command.

As we run this code from the VBA Macro editor, it selects the last row of our dataset.

Selecting Last Row Using End(xlUp)


2. Using End(xlDown) Method

This time, we will start from the top and go down to the last non-empty row of the sheet. We will use the following code to do so:

Sub EndxlDown()
Dim ws As Worksheet
Dim lastRow As Long
Set ws = ThisWorkbook.Worksheets("EndxlDown")
lastRow = ws.Range("B4").End(xlDown).Row
ws.Rows(lastRow).Select
End Sub

Code with End(xlDown)

VBA Breakdown

lastRow = ws.Range("B4").End(xlDown).Row
ws.Rows(lastRow).Select
  • The code starts from the B4 cell and then goes down to the last empty cell as it sets its row number as the value of the “lastRow” variable.
  • Then, we simply select the entire row with the ws.Rows(lastRow).Select command.

As soon as we run the code, Excel will automatically select the 14th row, which is the last non-empty row in this sheet.

Selecting Last Row applying End(xlDown)

If we have a dataset like the image below where we have a blank row inside the dataset (row 14), then the End(xlDown) method will fail to capture the last row correctly.

Discontinuous Dataset

In the following image, the code selects the 13th row as the last row of the dataset instead of the 15th row. This is because the End(xlDown) stops searching as soon as it finds any blank cell. So this method is not compatible with this kind of dataset.

End(xlDown) Method Ignores Values after Blank Row


Method 3 – Applying CurrentRegion Property

The Range.CurrentRegion property marks the region in the sheet surrounded by blank rows and columns. In our dataset, the Range.CurrentRegion property will mark the B4:E14 range as the currently used region. We will use the following code to select the last cell using this property:

Sub CurrentRegion()
Dim ws As Worksheet
Dim lastRow As Long
Dim Rng As Range
Set ws = ThisWorkbook.Worksheets("CurrentRegion")
Set Rng = ws.Range("B4").CurrentRegion
lastRow = Rng.Rows(Rng.Rows.Count).Row
ws.Rows(lastRow).Select
End Sub

VBA Code with CurrentRegion Property

VBA Breakdown

Set Rng = ws.Range("B4").CurrentRegion
lastRow = Rng.Rows(Rng.Rows.Count).Row
ws.Rows(lastRow).Select
  • The first line uses the .CurrentRegion property to set the value of the Range variable “Rng”. It starts from the B4 cell and adds all the non-empty cells that are adjacent to this cell. In this case the value of the “Rng” variable will be B4:E14.
  • The second line sets the “lastRow” variable to the row number of the last cell in the last row of the “Rng” range. The Rows.Count property returns the total number of rows in the range, and Rows(Rng.Rows.Count) refers to the last row in the range. Finally, the Row property returns the row number of the last cell in that row.
  • The final line selects the last row of the “Rng” range.

As we execute the code, Excel will select the 14th row which is the last row of the range.

Applying CurrenRegion Property to Select Last Row

Like the End(xlDown) method, the Range.CurrentRegion property needs a continuous dataset to select the last row correctly. If we have a blank row inside our dataset, then this property will mark the row before the blank row as the last row. So this method is not suitable for a jagged dataset.


Method 4 – Using UsedRange Property

The Worksheet.UsedRange property returns the range of a worksheet that is being used. The range may contain blank rows or columns or cells inside it. Here, our dataset contains a blank row, but the UsedRange property will take the entire B4:E15 range as the used range.

Jagged Dataset

We will use the UsedRange property in the following code to select the last row of our dataset:

Sub UsedRange()
Dim ws As Worksheet
Dim lastRow As Long
Dim Rng As Range
Set ws = ThisWorkbook.Worksheets("UsedRange")
Set Rng = ws.UsedRange
lastRow = Rng.Rows(Rng.Rows.Count).Row
ws.Rows(lastRow).Select
End Sub

VBA Code with UsedRange Property

VBA Breakdown

Set Rng = ws.UsedRange
lastRow = Rng.Rows(Rng.Rows.Count).Row
ws.Rows(lastRow).Select
  • This portion of the code will mark the B4:E15 range as the used range of the sheet using the UsedRange property, and set this range as the value for the “Rng” variable.
  • Then we set the row number of the last row of this range as the value of the “lastRow” variable, which is 15.
  • Finally, the code selects the entire 15th row with the ws.Rows(lastRow).Select command.

As we can see in the image below, the code will select the 15th row as the last row despite the presence of a blank row inside the dataset. So this method works well with discontinuous datasets.

Excel Selects the Correct Last Row in Discontinuous Dataset

However, there is a problem. In the following image, we changed the background color of the 16th row and then removed the color to set it to no fill.

Formatting Row

Now, if we run the code again, the code will identify the 16th row as the last row. Despite removing the background color of the 16th row, the code identifies the entire B4:E16 range as the used range. So in this scenario, this code will not work correctly.

UsedRange Property Includes Formatted Row in the Used Range


Method 5 – Using SpecialCells Method

The Range.SpecialCells property returns the cells or range which matches special types and values. In this case, we will insert xlCellTypeLastCell inside the Range.SpecialCells property to find out the last cell of our dataset. Here is the VBA code:

Sub SpecialCells()
Dim ws As Worksheet
Dim lastRow As Long
Set ws = ThisWorkbook.Worksheets("SpecialCells")
lastRow = ws.Cells.SpecialCells(xlCellTypeLastCell).Row
ws.Rows(lastRow).Select
End Sub

VBA Code with SpecialCells Property

VBA Breakdown

lastRow = ws.Cells.SpecialCells(xlCellTypeLastCell).Row
ws.Rows(lastRow).Select
  • The first line sets the ”lastRow” variable to the row number of the last cell that contains data, a formula, or formatting in the worksheet. The Cells property returns a range object that represents all cells on the worksheet, and the SpecialCells(xlCellTypeLastCell) method returns a range object that represents the last used cell on the worksheet. Finally, the Row property returns the row number of the last used cell.
  • The second line simply selects the entire last row.

As we run the code from the Macros tab, the code selects the last row of the dataset despite the presence of a blank row inside the dataset. So, this property works well with discontinuous datasets.

Applying SpecialCells Method to Select Last Row

However, like the UsedRange property, this property is sensitive to formatting, remembers deleted formatting, and sets the last row accordingly.


Method 6 – Using Find Method

The Range.Find method in VBA is used to search for a specific value or text in a range of cells and returns a range object that represents the first cell that matches the search criteria. In this instance, we will use this method to find the last used row of our sheet.

Here is the VBA code:

Dim lastRow As Long
Set ws = ThisWorkbook.Worksheets("Find")
lastRow = ws.Cells.Find(What:="*" _
, Lookat:=xlPart _
, LookIn:=xlFormulas _
, searchorder:=xlByRows _
, searchdirection:=xlPrevious).Row
ws.Rows(lastRow).Select
End Sub

VBA Code with Find Method to select last row in Excel

VBA Breakdown

lastRow = ws.Cells.Find(What:="*" _
, Lookat:=xlPart _
, LookIn:=xlFormulas _
, searchorder:=xlByRows _
, searchdirection:=xlPrevious).Row
ws.Rows(lastRow).Select
  • This line sets the “lastRow” variable to the row number of the last cell that contains data, a formula, or formatting in the worksheet. The Find method is called on the Cells property of the “ws” worksheet object, with the search criteria specified by the parameters:
    • What:=”*”: means to search for any non-empty cell. The “*” sign indicates any value of any type i.e. numbers, texts, dates, etc.
    • Lookat:=xlPart: means to search for the value specified in the What parameter anywhere within the cell, not just exact matches.
    • LookIn:=xlFormulas: means to search within the cell formulas.
    • searchorder:=xlByRows: means to search row by row.
    • searchdirection:=xlPrevious: means to search in reverse order, from the bottom to the top.

This line looks for all non-empty values in the entire sheet and then returns the row number of the last non-empty cell.

  • The final line Rows(lastRow).Select selects the entire last row.

As seen in the image below, as we run the code, it selects the last row of the dataset despite the dataset being discontinuous.

Selecting Last Row Using Find Method

Also, unlike the previous methods, this method does not consider the formatting of rows and ignores the 17th row which we formatted by changing its background color, selecting the 15th row as the last non-empty row.

Find Method Selects Only Rows with Values and Ignores Formatting


Method 7 – Selecting Last Row of Table Objects

In this method, we will formulate a VBA code to select the last row of a table in Excel. Here is the VBA code:

Sub TableObject()
Dim tbl As ListObject
Dim lastRow As Long
Set tbl = ActiveSheet.ListObjects("Table1") ' replace "Table1" with your table name
lastRow = tbl.ListRows.Count
tbl.ListRows(lastRow).Range.Select
End Sub

VBA Code to Select Last Row of a Table in Excel

VBA Breakdown

Dim tbl As ListObject
Dim lastRow As Long
  • These lines declare two variables, ”tbl” and “lastRow” .”tbl” is a ListObject type variable that will be used to refer to the table, and “lastRow” is a Long type variable that will be used to store the last row number of the table.
Set tbl = ActiveSheet.ListObjects("Table1")
  • This line sets the tbl variable to the ListObject named “Table1” in the active worksheet. You can replace “Table1” with the name of the table you want to select the last row from.
lastRow = tbl.ListRows.Count
tbl.ListRows(lastRow).Range.Select
  • The first line counts the row number of the Table and assigns it as the value of the “lastRow” variable.
  • The second line selects the last row of the table by calling the ListRows property of the Table, passing the lastRow variable as an index.

As soon as we run the code, it will select the last row of the Table.

Selecting Last Row of Table


Excel VBA to Find Last Row and Paste Data

In this method, we will find the last row of our dataset and paste some values in the next row down from the last row. Here, we have the data of an employee in the G5:J5 range, and will paste it in the 15th row.

Dataset to Add Row after the Last Row

Here is the VBA code to paste the data:

Sub LastRowPaste()
Dim last_row As Long
last_row = Cells(Rows.Count, "B").End(xlUp).Row
Range("G5:J5").Copy Destination:=Cells(last_row + 1, "B")
End Sub

VBA Code to Find Last Row and Paste Values After that Row

VBA Breakdown

This code looks for the last row by using the End(xlUp) property. Then, it pastes the values in the G5:J5 range using the Range.Copy property, with the value of the Destination parameter being the row after the last row in column B.

As we can see from the video, the code appends the dataset with the new values by pasting them in the row right below the last row.


Frequently Asked Questions

1. How do I select data until the last row with a keyboard shortcut?

  • Click on the cell where you want to start your selection.
  • Press the Ctrl + Shift + End keys on your keyboard.

This will select all the cells from the current cell to the last cell in the worksheet’s lower-right corner.

Note that this shortcut selects all the cells, including empty cells. If you want to select only the cells with data, press Ctrl + Shift + Down Arrow instead of Ctrl + Shift + End.

2. How do I select multiple rows with Excel VBA?

Use the Range object and specify the range of rows you want to select. Here’s an example code that selects rows 1 to 5:

Sub SelectMultipleRows()
Rows("1:5").Select
End Sub

3. How do I find the last used column with VBA?

Use the End method with the xlToLeft parameter. Here’s an example code that demonstrates how to find the last used column in row 1 of the active worksheet:

Sub FindLastUsedColumn()
Dim lastCol As Long
lastCol = Cells(1, Columns.Count).End(xlToLeft).Column
MsgBox "The last used column in row 1 is " & lastCol
End Sub

Download Practice Workbook

Get FREE Advanced Excel Exercises with Solutions!
Adnan Masruf
Adnan Masruf

Adnan Masruf, holding a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, plays a pivotal role as an Excel & VBA Content Developer at ExcelDemy. His deep passion for research and innovation seamlessly aligns with his dedication to Excel. In this capacity, Masruf not only skillfully addresses challenging issues but also exhibits enthusiasm and expertise in gracefully navigating intricate situations, underscoring his steadfast commitment to consistently delivering exceptional content. His interests... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo