Excel VBA: Find the Last Cell with Data in a Column

In Excel VBA, use Cells(Rows.Count, columnNumber).End(xlUp).Row to find the last nonblank cell’s row in a column. Check for an empty column first, because starting at the bottom of an entirely empty column makes End(xlUp) return row 1.

A basic VBA macro for the last data row

This example checks column C on a worksheet named Data, then reports the last row containing a value or formula. If C2:C8 contains entries and the cells below are empty, it reports row 8. Change the worksheet name or set targetColumn to another column number: A is 1, B is 2, C is 3, and so on.

Option Explicit

Sub FindLastDataRowInColumn()
    Dim ws As Worksheet
    Dim targetColumn As Long
    Dim lastRow As Long

    Set ws = ThisWorkbook.Worksheets("Data")
    targetColumn = 3 ' Column C

    ' CountA lets the macro distinguish an empty column from row 1.
    If Application.CountA(ws.Columns(targetColumn)) = 0 Then
        MsgBox "Column C is empty."
        Exit Sub
    End If

    ' Start at the sheet's last row and move upward to the first nonblank cell.
    lastRow = ws.Cells(ws.Rows.Count, targetColumn).End(xlUp).Row
    MsgBox "Last data row in column C: " & lastRow
End Sub

Before running a macro, save a copy of the workbook. VBA changes cannot be reversed with Ctrl+Z; this example only displays a message, but the reminder matters when adapting it to make changes. Store VBA in a macro-enabled .xlsm workbook if you need the code to remain available after closing the file.

How End(xlUp) finds the row

ws.Rows.Count returns the final row number for that worksheet. The expression ws.Cells(ws.Rows.Count, targetColumn) therefore refers to the bottom cell in the chosen column. Calling .End(xlUp) moves upward from there, in the same general way as pressing Ctrl+Up in a worksheet, and stops at the next nonempty cell. The final .Row gives that cell’s row number.

This approach searches the whole column, not just the block directly above the active cell. For example, if column C has data in C2:C5 and C12, with blanks in between, the result is 12. It does not stop at the first gap it encounters from the top. That makes it useful for lists that may contain blank rows, provided the data really is in the column being checked.

Use the worksheet-qualified form, such as ws.Cells(ws.Rows.Count, targetColumn), rather than relying on the active sheet. Without a worksheet reference, VBA may inspect whichever sheet happens to be active when the macro runs. That can produce a plausible but incorrect row number.

Return a row number from a reusable function

If several procedures need the same check, put a function in a standard VBA module. This version returns 0 for a completely empty column; otherwise it returns the row number of the last nonblank cell. The caller can then use the result to build a range or control a loop.

Public Function LastDataRow(ByVal ws As Worksheet, _
                            ByVal columnNumber As Long) As Long
    ' Return 0 when the column has no constants or formulas.
    If Application.CountA(ws.Columns(columnNumber)) = 0 Then
        LastDataRow = 0
    Else
        LastDataRow = ws.Cells(ws.Rows.Count, columnNumber).End(xlUp).Row
    End If
End Function

For example, a procedure could call LastDataRow(ThisWorkbook.Worksheets("Data"), 3) and store the result in a Long variable. A return value of 0 means there is no content to process; a positive result identifies the final row. Keeping the empty-column convention explicit prevents later code from treating row 1 as a real record when no records exist.

What counts as data—and what does not

End(xlUp) treats a cell containing a constant or a formula as nonblank. A formula that displays an empty string, such as one returning "", is still a formula in the cell, so it can be treated as the last occupied cell even though it looks empty on the sheet. The CountA test also counts such formulas. This is often the right definition for a worksheet whose formulas are part of its data model, but it may not match a request for the last visibly displayed value.

Formatting alone is different. A cell with a fill color, border, or number format but no value or formula is not a nonblank cell for this End(xlUp) check. This is one reason it can give a more useful answer than asking where Excel’s used range ends: formatting applied far below a table can enlarge the used range without adding actual cell contents.

Before choosing a method, decide what “has data” means for the task:

Any constant or formula: use the standard End(xlUp) pattern.
Displayed results only: formulas returning an empty string may need to be excluded with extra logic.
Formatting counts as use: this is a different requirement; inspect formatting or the used range rather than treating a cell’s contents as the criterion.

Handling formulas that display as blank

A common surprise is that the macro returns a row whose cell looks blank. If that cell contains a formula such as =IF(A20="","",A20), Excel displays an empty string when A20 is empty, but the formula remains in the cell. Therefore the basic method finds it. This is not a failure of End(xlUp); it is a difference between “cell contains a formula” and “cell displays visible text or a number.”

If your task is to process only visible results, decide how to handle formulas that return empty strings before writing the loop. One option is to scan upward and test each cell’s displayed value, while separately defining what to do with errors and whitespace. For example, a cell containing one or more spaces is not the same as a truly empty cell. A broad rule that trims text may be appropriate for a cleaned text list, but it could incorrectly discard meaningful spaces or values in other types of data. Use the business rule for the column, rather than assuming that every visually blank cell is actually empty.

Choosing between End, Find, and UsedRange

These Excel techniques answer related but different questions. For a known column, End(xlUp) is usually the clearest choice: it returns a row number and naturally searches upward through gaps. For a search that must inspect particular contents or span multiple columns, the Range.Find method can be more flexible, but its search options should be supplied deliberately. Excel retains some Find settings between calls, so relying on omitted arguments can make VBA behave differently from what a reader expects.

UsedRange describes the worksheet area Excel considers used, not necessarily the final record in a particular column. It may include cells that were formatted or edited and later cleared. Likewise, pressing Ctrl+End moves to the end of Excel’s used area, which may not be the last data row in your table. CurrentRegion is another alternative, but blank rows or columns can divide a region, so it is not a general substitute for searching one entire column.

Choose End(xlUp) for a last occupied row in one known column.
Choose Find when the search needs more control over what qualifies as a match.
Choose UsedRange when you actually need Excel’s used worksheet area, not the last record in a field.

Common errors and practical safeguards

A frequent mistake is writing Cells(Rows.Count, "C").End(xlUp).Row without qualifying Cells and Rows. That code relies on the active worksheet. It is safer to use ws.Cells(ws.Rows.Count, 3) so both references point to the same intended sheet. Another mistake is hard-coding a worksheet’s final row number. Using ws.Rows.Count adapts to the worksheet’s row capacity and makes the code easier to maintain.

Also confirm that the column you search is a reliable indicator of a record. If column A contains optional notes but column B is always populated for every record, using A can stop too early. Conversely, if a formula is filled far below the visible records, the method may correctly identify the formula row even if it is not a meaningful record. The macro finds the last nonblank cell in the chosen column; it cannot infer which rows your organization considers valid records.

For a multi-column table, select a column that is populated on every genuine record, such as an ID field, and use that column consistently. If no single column is guaranteed to be populated, you may need to search the table area for the last row containing anything instead of checking just one field.

Running the macro in Excel for Microsoft 365

On Windows, open the VBA editor with Alt+F11, insert a standard module through Insert > Module, and paste the code. In Excel, run a macro with Developer > Macros, select its name, and choose Run; Alt+F8 opens the macro list. If the Developer tab is hidden, enable it through File > Options > Customize Ribbon and select Developer.

In Excel for Microsoft 365 on Mac, the Developer tab also provides access to Visual Basic and Macros when enabled. Option+F11 commonly opens the Visual Basic Editor, and Option+F8 opens the macro dialog; on some Mac keyboards, you may also need to hold Fn. Keyboard behavior can depend on macOS keyboard settings, so the ribbon or menu commands are a useful alternative. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) to preserve VBA code.

Macros may be blocked by security settings, particularly for files downloaded from the internet or received by email. Only enable code from a source you trust, and follow your organization’s macro policy. The macro does not require a reference to an external library; it uses Excel’s built-in worksheet and VBA objects.

Frequently Asked Questions

How do I find the last used row in column A with VBA?
Use Cells(Rows.Count, 1).End(xlUp).Row on the intended worksheet. Qualify the references, for example ws.Cells(ws.Rows.Count, 1).End(xlUp).Row, and check for an empty column if row 1 would be an invalid result.

Does End(xlUp) skip blank rows?
It can find a nonblank cell above gaps because it starts at the bottom of the column and moves upward. A blank in the middle does not by itself make the search stop at that gap.

Why does VBA return a row that looks empty?
The cell may contain a formula that returns an empty string, or it may contain spaces. The basic method considers a formula occupied even when its displayed result looks blank.

Why does an empty column return row 1?
With no occupied cell above the starting point, End(xlUp) reaches the top of the column. Use a check such as Application.CountA(ws.Columns(columnNumber)) = 0 to handle the empty case explicitly.

Does this method find the last cell with formatting?
No. It finds the last nonblank cell by contents or formula, not a cell that only has formatting. Excel’s used range may include formatting, so it can extend farther than the row found by this method.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.