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.

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

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

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

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

lastRow = ws.Range("B4").End(xlDown).Row
ws.Rows(lastRow).Select

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

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.

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.

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

Set Rng = ws.Range("B4").CurrentRegion
lastRow = Rng.Rows(Rng.Rows.Count).Row
ws.Rows(lastRow).Select

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

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.

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

Set Rng = ws.UsedRange
lastRow = Rng.Rows(Rng.Rows.Count).Row
ws.Rows(lastRow).Select

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.

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.

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.

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

lastRow = ws.Cells.SpecialCells(xlCellTypeLastCell).Row
ws.Rows(lastRow).Select

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.

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

lastRow = ws.Cells.Find(What:="*" _
, Lookat:=xlPart _
, LookIn:=xlFormulas _
, searchorder:=xlByRows _
, searchdirection:=xlPrevious).Row
ws.Rows(lastRow).Select

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

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.

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

Dim tbl As ListObject
Dim lastRow As Long

Set tbl = ActiveSheet.ListObjects("Table1")

lastRow = tbl.ListRows.Count
tbl.ListRows(lastRow).Range.Select

As soon as we run the code, it will select the last row of the 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.

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

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