How to Find Last Row, Column, and Cell using VBA in Excel

Let’s say you’re a sales analyst at a company, and you’ve been tracking yearly sales data in Excel. Each row in this spreadsheet represents the sales figures for a different year.

Over the years, as your company grows and expands, more rows are added to this table to accommodate the new data for each year.

At some point, you need to quickly discover how many years of sales data are in your spreadsheet, especially when preparing annual reports or forecasts. You can use a simple VBA code to find the last row of data in your worksheet.

In VBA, there are several methods to find the last row, column, or cell with data in a worksheet. Each method has advantages and may be more suitable depending on the data. Here are the main methods:

Each method serves different purposes and works differently depending on your data’s layout (sparse, contiguous, formatted, etc.). It’s important to choose the method that best fits your data.

Find the Last Row, Column, or Cell using the VBA End Method

1. Define the cell or the range from where you want to navigate to the last row.
2. After that, enter a dot to get the list of properties and methods.
3. Select or type “End” and enter starting parentheses.
4. Use the argument that you want to use.
5. Further, use the address property to get the address of the cell.

MsgBox Range("A1").End(xlDown).Address

When you run the above code, it shows you a message box with the row number of the last non-empty cell.

Find the Last Column using VBA

Now, let’s say you want to find the last column. In that case, instead of using the “xlDown” constant, you need to use the “xlRight”, and if you want to select that cell instead of having the address, then you can use the “select” method. Consider the following method.

Range("A1").End(xlToRight).Select

Find the Last Cell

Using the same method, you can also get the last cell, which is a non-empty cell. To write this code, you need to know the last row and column.

Sub vba_last_row()
  Dim lRow As Long
  Dim lColumn As Long
  lRow = Range("A1").End(xlDown).Row
  lColumn = Range("A1").End(xlToRight).Column
  Cells(lRow, lColumn).Select
End Sub

To understand the above code, we need to split it into three parts.

1. In the FIRST part, you have declared two variables to store the row and the column number.
2. In the SECOND part, you have used “End” with the “xlDown” and then the Row property to get the row number of the last, and in the same way, you have used the “End” with the “xlToRight” and then the “Column” property to get the column number of the last column.
3. In the THIRD part, by using the last column number and last row number, refer to the last cell and select it.

Note: If you want to select a cell in the different worksheet using the last row and last column method, you must activate that worksheet first.

Last Row, Column, and Cell using the Find Method

The Find method in VBA effectively searches for specific data within a range, making it incredibly useful for locating the position of data, such as the last row, last column, or a specific cell containing a particular value.

The below code starts its search from the very first cell in your sheet, “A1,” and it looks through all the rows from the bottom up. It doesn’t matter if the data is just part of a cell (like a few characters in a larger text) or if it’s hidden within a formula; this code will still find the last row where something was entered.

Sub vba_last_row()
Dim iRow As Long
iRow = Cells.Find(What:="*", _
                    After:=Range("A1"), _
                    LookAt:=xlPart, _
                    LookIn:=xlFormulas, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlPrevious, _
                    MatchCase:=False).Row
MsgBox iRow
End Sub

Once it identifies the last row with a value, the code stores the row number in an iRow variable. The MsgBox function then pops up a message box showing this row number. And for column number:

Sub vba_last_row()
Dim iColumn As Long
iColumn = Cells.Find(What:="*", _
                    After:=Range("A1"), _
                    LookAt:=xlPart, _
                    LookIn:=xlFormulas, _
                    SearchOrder:=xlByColumns, _
                    SearchDirection:=xlPrevious, _
                    MatchCase:=False).Column
MsgBox iColumn
End Sub

To get the cell address of the last cell.

Sub vba_last_row()
Dim iColumn As Long
Dim iRow As Long
iColumn = Cells.Find(What:="*", _
                    After:=Range("A1"), _
                    LookAt:=xlPart, _
                    LookIn:=xlFormulas, _
                    SearchOrder:=xlByColumns, _
                    SearchDirection:=xlPrevious, _
                    MatchCase:=False).Column                  
iRow = Cells.Find(What:="*", _
                    After:=Range("A1"), _
                    LookAt:=xlPart, _
                    LookIn:=xlFormulas, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlPrevious, _
                    MatchCase:=False).Row                   
Cells(iRow, iColumn).Address
End Sub

Sub FindLastRowUsedRange()
    Dim lastRow As Long
    With ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your sheet's name
        lastRow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
    End With
    MsgBox "The last row used is: " & lastRow
End Sub

It determines the last row by checking the total count of rows that have data and storing this number in the lastRow variable.

In the end, it uses a message box to show you the result. It concatenates the text “The last row used is: ” with the number stored in lastRow, effectively giving you a message box that tells you which row is the last one with data on your specified worksheet.

Below is the code that you can use to find the last column from the data.

The code below shows you a message box with the last column number used in the worksheet.

Sub FindLastColumnUsedRange()
    Dim lastColumn As Long
    With ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your sheet's name
        lastColumn = .UsedRange.Columns(.UsedRange.Columns.Count).Column
    End With
    MsgBox "The last column used is: " & lastColumn
End Sub

It creates a variable called lastColumn to store the number of the final columns where data is found. It focuses on the workbook you’re using, particularly on “Sheet1”.

It calculates the last column by counting the total number of columns that contain data and saves this number in the lastColumn variable. In the end, it shows you a message box with the column number of the last column.

And here’s the code for finding the last cell from the data using the UsedRange.

Sub FindLastCellUsedRange()
    Dim lastRow As Long, lastColumn As Long
    Dim lastCell As Range
    With ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your sheet's name
        lastRow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
        lastColumn = .UsedRange.Columns(.UsedRange.Columns.Count).Column
        lastCell = .Cells(lastRow, lastColumn)
    End With
    MsgBox "The last cell used is: " & lastCell.Address
End Sub

In this code, you have two variables to store the number of the last row and column that contain data. Additionally, it declares a variable to represent the last cell where data is entered.

Within the worksheet, the code calculates which row and column are the last ones used and then identifies the exact cell at the intersection of these two. And in the end, it shows you a new message box with the cell address.