Excel VBA to Select Last Row

In this article, we will discuss Excel VBA to select the last row. If users want to append a large dataset, they have to add a new row after the last row. In large datasets, it becomes tedious and time-consuming to go to the last row. Even though we can use some keyboard shortcuts to reach the bottom row of a dataset, they do not consider the rows after a blank row. So, in this scenario, we are going to present some Excel VBA codes to select the last row of a dataset.

Excel VBA to Select Last Row: 7 Ways

Today, we will talk about Excel VBA codes to select the last row of a dataset. Here, the last row means the last row of a dataset that contains values. In this case, we have a dataset that contains the information of some employees. We will use 7 VBA codes to select the last row of this dataset.

1. Using End(xlUp) Method

In the first method, we will use the End(xlUp) method to select the last row of our dataset. We will apply the method in the following 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 by launching VBA Macro editor, the code selects the last row of our dataset.

2. Using End(xlDown) Method

In this instance, we will select the last row of our dataset using the End(xlDown) method. Here, 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.

Now, 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.

As we can see 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.

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, the 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.

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. 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.

5. Applying 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 too, sensitive to formatting and remembers the deleted formatting, and sets the last row accordingly.

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 to do so,

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 from 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 and selects the 15th row as the last non-empty row.

7. Selecting Last Row of Table Objects

We can select a row based on cell value with VBA. 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 at the immediate row from the last row. Here, we have the data of an employee in the G5:J5 range. We will paste them 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?

We select the data up to the last row by using keyboard shortcuts. Here are the steps to follow,

• 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 in the lower-right corner.

Note that this shortcut selects all the cells, including empty cells. If you want to select only the cells with data, you can press Ctrl + Shift + Down Arrow instead of Ctrl + Shift + End. This will select all the cells from the current cell to the last cell in the column that contains data.

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

You can select multiple rows in Excel VBA by using the Range object and specifying 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 to find last used column in VBA?

To find the last used column in VBA, you can 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

You can download the practice book here.

Conclusion

In this article, we have discussed 7 Excel VBA codes to select the last row of a dataset. We have also talked about the pros and cons of each method. These methods will allow users to select the last row of their dataset no matter what the configuration of the dataset is: continuous or discontinuous. This article will also help them to choose methods according to their needs.