How to Find the Last Occupied Cell in a Row or Column in Excel with VBA

One of the most common tasks when working with spreadsheets in Excel and VBA is determining the last occupied cell in a row or column. This is especially useful when you need to efficiently input data, as identifying the last cell with data allows you to easily navigate to the first available cell to enter new values. This tutorial will guide you on how to find the last occupied cell in Excel using VBA, streamlining your workflow.

How to Find the Last Occupied Cell with a Loop in VBA

One option for finding the last occupied cell is using a VBA loop. Although it’s not the most efficient in terms of performance, this method allows you to loop through all the cells in a range (either a row or column) and check for empty cells. Below is an example of how to write to the first empty cell after identifying the last occupied row:

Sub getLastRow()
i = 1
 Do
     If IsEmpty(Cells(i, 1)) = True Then Exit Do
     i = i + 1
 Loop
lastRow = i - 1
Cells(lastRow + 1, 1) = "First available cell" 
End Sub

This code loops through column A and finds the last occupied row. Then, it writes to the first available cell right after the last row with data. You can also adapt this method to find the last occupied column in a specific row. Here’s how you can do it:

Sub getLastColumn()
 j = 1
 Do
     If IsEmpty(Cells(1, j)) = True Then Exit Do
     j = j + 1
Loop 
lastColumn = j - 1
Cells(1, lastColumn + 1) = "First available cell" 
End Sub

Quick Method: Find the Last Occupied Cell without a Loop

If you prefer a faster and more performance-efficient approach, we recommend using the method described below. This method is ideal for finding the last occupied cell in a specific column or specific row without the need to loop through all the cells. Here’s how to do it: To find the last occupied row in a column, you can use the following code: Variable = Cells(Rows.Count, “Column Reference”).End(xlUp).Row This code will find the last occupied row in the specified column. Here’s an example of how to implement it:

Sub getLastRow()
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
Cells(lastRow + 1, 1) = "First available cell"
End Sub

Similarly, if you need to find the last occupied column in a specific row, you can use the following code: Variable = Cells(Row Index, Columns.Count).End(xlToLeft).Column This command returns the index of the last occupied column in the specified row. Here’s an example:

Sub getLastColumn()
lastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
Cells(1, lastColumn + 1) = "First available cell"
End Sub

This method is ideal for optimizing your VBA code in Excel and improving performance, especially when working with large spreadsheets.

Other Methods for Finding the Last Occupied Cell

There are several methods to find the last occupied cell in Excel using VBA. In addition to the methods mentioned above, here are two additional approaches that may be useful depending on the context:

Using UsedRange

The UsedRange object allows you to get the range of cells that are in use, which is useful when you need to quickly determine the area containing data without looping through all cells. Here’s how to find the last occupied row and column:

Sub getLastRowUsedRange()
    Dim lastRow As Long
    lastRow = ActiveSheet.UsedRange.Rows.Count
    MsgBox "The last occupied row is: " & lastRow
End Sub

Sub getLastColumnUsedRange()
    Dim lastColumn As Long
    lastColumn = ActiveSheet.UsedRange.Columns.Count
    MsgBox "The last occupied column is: " & lastColumn
End Sub

Using Find

The Find method is a very fast option for finding the last occupied cell in a row or column. Use this technique if you prefer a precise and efficient search. Below is an example to find the last occupied row:

Sub getLastRowFind()
    Dim lastRow As Long
    On Error Resume Next
    lastRow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    On Error GoTo 0
    MsgBox "The last occupied row is: " & lastRow
End Sub

Similarly, you can use Find to find the last occupied column:

Sub getLastColumnFind()
    Dim lastColumn As Long
    On Error Resume Next
    lastColumn = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    On Error GoTo 0
    MsgBox "The last occupied column is: " & lastColumn
End Sub

Want to learn more about boosting your productivity in Excel with VBA? Contact us!