How To Find Last Row using SpecialCells [Excel VBA]

This tutorial will cover how to find the Last Used Row Using SpecialCells in Excel VBA.

The SpecialCells method in VBA has a specific option (Last Cell) that is specifically geared to finding the last used cell in a worksheet. The last cell is the intersection of the last used row and last used column. Using that information, we can determine the last row or the last column.

We can access SpecialCells directly in Excel. In the Home Ribbon, go to Find and Select, select Go To Special, choose the Last Cell option and click ok.

Find Last Row

Note: In all examples, the worksheet code name wsInv is being used. You could refer to the worksheet directly instead: ThisWorkbook.Sheets(“Invoice”)

You can call SpecialCells from any cell in the worksheet and it will give you the same answer as the last cell.

Sub Find_Last_Row()
''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    'Find Last Row
''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim lrow As Long
lrow = wsInv.Range("A1").SpecialCells(xlCellTypeLastCell).row
''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    'Find Last Column
''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim lcol As Long
lcol = wsInv.Range("A1").SpecialCells(xlCellTypeLastCell).Column
''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    'Find Last Address
''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim sAdd As String
sAdd = wsInv.Range("A1").SpecialCells(xlCellTypeLastCell).Address
End Sub

Example 1: Identify and Select Data Range

When the last column is known, we can find the last row using the previous code and determine the data range. When the last column is not known, we can find the address of the last cell and use it to determine the data range.

Sub Find_Last_Row()
''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    'Select range when last column is known
''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim lrow As Long
lrow = wsInv.Range("A1").SpecialCells(xlCellTypeLastCell).row
wsInv.Range("A1:J" & lrow).Select
''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    'Select range when last column is not known
''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim sAdd As String
sAdd = wsInv.Range("A1").SpecialCells(xlCellTypeLastCell).Address
wsInv.Range("A1:" & sAdd).Select
End Sub

Example 2: Copy Data Range to New Sheet

We will copy data from current sheet wsInv onto sheet: CopyTo (code name: wsCopyTo).

Sub Find_Last_Row()
''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    'Copy using last cell address
''''''''''''''''''''''''''''''''''''''''''''''''''''''''
wsCopyTo.Cells.Clear
Dim sAdd As String
sAdd = wsInv.Range("A1").SpecialCells(xlCellTypeLastCell).Address
wsInv.Range("A1:" & sAdd).Copy wsCopyTo.Range("A1")
wsCopyTo.Columns.AutoFit
End Sub

Example 3: Paste Data at Bottom of Current Data Range

We will copy data from sheet: CopyFrom (code name: wsCopyFrom) and paste it at bottom of our data range in wsInv sheet. Instead of copying the full range of data, we will copy the data line by line. No reason for this. It’s just to make this example a bit different from my other blogs on last row.

Sub Find_Last_Row()
'Find last row of current data range
Dim lrowCopyFrom As Long
lrowCopyFrom = wsCopyFrom.Range("A1").SpecialCells(xlCellTypeLastCell).row
Dim offrowInv As Long
Dim i As Long
For i = 2 To lrowCopyFrom
    'Find the first unused row
    offrowInv = wsInv.Range("A1").SpecialCells(xlCellTypeLastCell).row + 1
    'Copy the data over
    wsCopyFrom.Range("A" & i & ":J" & i).Copy wsInv.Range("A" & offrowInv)
Next i
End Sub

Bonus Example: Delete Blank Rows

Use below code to delete blank rows in a worksheet within our data range. The code will find the last used row using SpecialCells and then loop backwards till row 1, deleting blank rows as the loop progresses.

Sub Find_Last_Row()
Dim lrow As Long
lrow = wsInv.Range("A1").SpecialCells(xlCellTypeLastCell).row
If lrow > 1 Then
    Dim i As Long
    For i = wsInv.Range("A1").SpecialCells(xlCellTypeLastCell).row To 1 Step -1
        If WorksheetFunction.CountA(wsInv.Rows(i)) = 0 Then
            wsInv.Rows(i).Delete
        End If
    Next
End If
End Sub

Related Posts