Finding the Last Row with Data in VBA

When working with data in Excel using VBA, it’s a common task to determine the last row containing data in a worksheet. This is crucial for iterating through data, performing calculations, or adding new information. However, simply counting rows using Range.Rows.Count can lead to inaccurate results if the worksheet isn’t completely filled. This tutorial will guide you through various methods to reliably find the last row containing data in a VBA project.

The Problem with Simple Counting

A naive approach might involve using Range("A1", Range("A1").End(xlDown)).Rows.Count. While this works correctly if there are multiple rows with data, it fails when only the first cell (A1) contains data. In this scenario, the code incorrectly returns the maximum number of rows in Excel (1,048,576) instead of 1. This is because End(xlDown) continues to the very bottom of the sheet, even past any actual data.

Method 1: Working Upwards from the Bottom

A more robust approach is to start at the bottom of the column and work your way up until you find the last non-empty cell.

Sub FindLastRowBottomUp()
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("Sheet1") ' Replace "Sheet1" with your sheet name

    Dim k As Long
    k = sh.Range("A1048576").End(xlUp).Row

    ' k now holds the last row number with data in column A
    Debug.Print k 'Output the result to the immediate window
End Sub

This code starts at cell A1048576 (the last row in Excel) and uses End(xlUp) to move upwards until it finds the first non-empty cell. The Row property then extracts the row number, giving you the last row containing data. This method is generally reliable and efficient.

Method 2: Utilizing the `UsedRange` Property

Excel’s UsedRange property returns a Range object that represents the cells containing data. However, be aware that the UsedRange can sometimes include cells that were used but are now empty, especially if formatting has been applied.

Sub FindLastRowUsedRange()
    Dim sh As Worksheet
    Dim rn As Range
    Set sh = ThisWorkbook.Sheets("Sheet1")

    Dim k As Long
    Set rn = sh.UsedRange
    k = rn.Rows.Count + rn.Row - 1

    ' k now holds the last row number with data
    Debug.Print k
End Sub

This code first obtains the UsedRange of the worksheet. Then, it calculates the last row number by adding the number of rows in the UsedRange to the starting row of the UsedRange and subtracting 1. The subtraction is necessary because Rows.Count includes the starting row.

Method 3: Finding Constant Cells

This approach leverages the SpecialCells method to identify cells containing constants (values, formulas that evaluate to values, etc.).

Sub FindLastRowConstantCells()
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("Sheet1")

    Dim k As Long
    k = sh.Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count

    ' k now holds the last row number with data
    Debug.Print k
End Sub

This code finds all cells in column A that contain constants and counts them, effectively giving you the last row with data.

Method 4: Custom Function (Ron de Bruin’s `Last` Function)

A versatile solution is to use a custom function designed to find the last row and column. This function, originally created by Ron de Bruin, is a helpful addition to any VBA toolkit.

Function Last(choice As Long, rng As Range)
'Ron de Bruin, 5 May 2008
' 1 = last row
' 2 = last column
' 3 = last cell
    Dim lrw As Long
    Dim lcol As Long

    Select Case choice

    Case 1:
        On Error Resume Next
        Last = rng.Find(What:="*", _
                        After:=rng.Cells(1), _
                        Lookat:=xlPart, _
                        LookIn:=xlFormulas, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlPrevious, _
                        MatchCase:=False).Row
        On Error GoTo 0

    Case 2:
        On Error Resume Next
        Last = rng.Find(What:="*", _
                        After:=rng.Cells(1), _
                        Lookat:=xlPart, _
                        LookIn:=xlFormulas, _
                        SearchOrder:=xlByColumns, _
                        SearchDirection:=xlPrevious, _
                        MatchCase:=False).Column
        On Error GoTo 0

    Case 3:
        On Error Resume Next
        lrw = rng.Find(What:="*", _
                       After:=rng.Cells(1), _
                       Lookat:=xlPart, _
                       LookIn:=xlFormulas, _
                       SearchOrder:=xlByRows, _
                       SearchDirection:=xlPrevious, _
                       MatchCase:=False).Row
        On Error GoTo 0

        On Error Resume Next
        lcol = rng.Find(What:="*", _
                        After:=rng.Cells(1), _
                        Lookat:=xlPart, _
                        LookIn:=xlFormulas, _
                        SearchOrder:=xlByColumns, _
                        SearchDirection:=xlPrevious, _
                        MatchCase:=False).Column
        On Error GoTo 0

        On Error Resume Next
        Last = rng.Parent.Cells(lrw, lcol).Address(False, False)
        If Err.Number > 0 Then
            Last = rng.Cells(1).Address(False, False)
            Err.Clear
        End If
        On Error GoTo 0

    End Select
End Function

Sub TestLastFunction()
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("Sheet1")
    Dim lastRow As Long
    lastRow = Last(1, sh.Range("A:A"))
    Debug.Print lastRow
End Sub

This function searches for the last cell containing any character (*) and returns the corresponding row or column number based on the choice argument (1 for last row, 2 for last column).

Choosing the Right Method

Each method has its strengths and weaknesses. The UsedRange method is convenient but can be inaccurate if the sheet has been heavily modified. The bottom-up approach and the custom function are generally more reliable. The constant cells method may not work if the cells contain formulas that evaluate to empty strings. Choose the method that best suits your specific needs and data structure.