excelavon

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

VBA & macros · Updated November 3, 2026

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

In Excel VBA, the usual way to find the last row with data in column A is ws.Cells(ws.Rows.Count, "A").End(xlUp).Row. Start at the worksheet’s bottom row and move upward; if the column may be completely empty or contain data at Excel’s very last row, use Find instead for a more explicit result.

The standard End(xlUp) method

This short example finds the last occupied row in column A on a worksheet named Data. It returns the row number, which you can then use in a range or loop. Change the worksheet name and column letter to match your workbook.

Sub ShowLastRowInColumnA()
    Dim ws As Worksheet
    Dim lastRow As Long

    ' Use the workbook containing this VBA code.
    Set ws = ThisWorkbook.Worksheets("Data")

    ' Start at the bottom of column A and move upward.
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    MsgBox "Last occupied row in column A: " & lastRow
End Sub

On a worksheet with entries in A1, A2, and A7, this method returns 7, even if A3 through A6 are blank. It does not require the entries to be consecutive. The code reports the last occupied cell in the selected column, not the number of records; a header in A1 is included in the row number.

Before running a macro, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z. Save a workbook containing macros as an Excel Macro-Enabled Workbook (.xlsm) if you need to retain the code.

How End(xlUp) works

ws.Rows.Count gets the total row count for that worksheet, so the code does not rely on a hard-coded row limit. ws.Cells(ws.Rows.Count, "A") identifies the bottom cell in column A. The End(xlUp) method moves upward through that column in the same general way as pressing Ctrl+Up in Excel, and .Row returns the row number of the cell reached.

In current Excel worksheets, the bottom row is 1,048,576. Using Rows.Count is preferable to typing that number into the code: it makes the intent clear and uses the row count of the worksheet referenced by ws. The result is a Long, the appropriate VBA variable type for worksheet row numbers.

Blank cells between records do not stop the search when it starts at the bottom of a normally used column. For example, if A10 contains a value, A11:A20 are empty, and A21 contains another value, the method returns 21. It is checking for the lowest occupied cell, not looking for the first gap after a header.

Handle a completely empty column

If column A has no contents at all, the standard expression returns 1. That is because moving upward from the bottom of an empty column reaches its top cell; it does not return a special “no row” value. If your code needs to distinguish an empty column from a column whose last entry is in row 1, test the result or use Find, which can return no match.

For a simple check that treats formulas as contents, use CountA before relying on the End result. CountA counts cells containing formulas even when those formulas display an empty string.

Sub GetLastRowOrZero()
    Dim ws As Worksheet
    Dim lastRow As Long

    ' Use the workbook containing this VBA code.
    Set ws = ThisWorkbook.Worksheets("Data")

    If Application.CountA(ws.Columns("A")) = 0 Then
        lastRow = 0
    Else
        lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    End If

    MsgBox "Last occupied row: " & lastRow
End Sub

Here, zero is a value your code assigns to mean “no entries found”; worksheet rows themselves start at 1. Checking a whole column with CountA is straightforward, but for a very large workbook or more specialized definitions of “data,” choose a search method that matches what should count as occupied.

Use Find when you need a no-match result

The Find method is useful when you want to test explicitly for a match, or when the bottom worksheet row could itself contain data. It returns a Range for a match and Nothing if there is no match. This version searches column A for constants and formulas, including formulas that display an empty string, because it searches in formulas.

Sub FindLastRowInColumnA()
    Dim ws As Worksheet
    Dim foundCell As Range
    Dim lastRow As Long

    ' Use the workbook containing this VBA code.
    Set ws = ThisWorkbook.Worksheets("Data")

    ' Search backward through column A for a constant or formula.
    Set foundCell = ws.Columns("A").Find( _
        What:="*", _
        After:=ws.Cells(1, "A"), _
        LookIn:=xlFormulas, _
        LookAt:=xlPart, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, _
        MatchCase:=False, _
        SearchFormat:=False)

    If foundCell Is Nothing Then
        lastRow = 0
    Else
        lastRow = foundCell.Row
    End If

    MsgBox "Last occupied row: " & lastRow
End Sub

Specifying the Find options matters because Excel remembers several Find settings from earlier searches in the user interface or VBA. Providing the options makes the search behavior more predictable. In this example, xlPrevious searches backward, and After sets a starting point in the searched range. The code handles an empty column by assigning zero.

Choose the worksheet and column deliberately

Use a worksheet object such as ws for both the row count and the cell lookup. That keeps the search tied to the intended sheet. By contrast, unqualified references such as Cells, Rows.Count, or Range("A:A") refer to the active sheet in many VBA contexts. If a different sheet becomes active, the result may be wrong or the code may fail.

ThisWorkbook means the workbook that contains the VBA code. ActiveWorkbook means the workbook currently active in Excel, which may be different. If your macro is stored in a personal macro workbook but should inspect the active file, assign the intended workbook and worksheet explicitly instead of assuming they are the same.

To search another column, replace "A" with its letter, such as "C". You can also use a numeric column index: with a worksheet assigned to ws and a column number stored in colNumber, the cell reference is ws.Cells(ws.Rows.Count, colNumber). Column A is 1, B is 2, and so on.

What counts as data?

End(xlUp) and the Find example above treat cells containing constants or formulas as occupied, even if a formula displays an empty string such as "". A formula like =IF(B2="","",B2) is still a formula in the cell, so it can affect the last-row result. Error values and text are also contents. Formatting by itself, such as a fill color applied to an otherwise empty cell, does not make that cell a data entry for these methods.

This distinction matters in sheets that use formulas prefilled far below the visible records. If formulas extend through row 5000 but currently display blanks, a search in formulas can return row 5000. Decide whether “last row” means the last cell containing a formula or constant, or the last row with a meaningful displayed value. Those are different questions, and the right method depends on the layout and the definition you need.

Likewise, choose a column that is reliably populated for every record. If column A has occasional blanks but column B is always filled, the last occupied row in A may be above the end of the dataset. A last-row result is only as useful as the column used to determine it.

Common mistakes and edge cases

  • Assuming an empty column returns zero: End(xlUp) returns row 1 for a completely empty column. Add a check or use Find if zero is your chosen “not found” result.
  • Using the active sheet by accident: Qualify the range with the worksheet variable, as in ws.Cells, so the macro searches the intended sheet.
  • Ignoring formulas that look blank: A formula returning an empty string is still a formula. Use a search strategy consistent with whether formulas count as occupied.
  • Putting data in the final worksheet row: End(xlUp) is normally used when the bottom row is blank. If that row could be occupied, use Find to avoid the special navigation behavior of starting on a filled bottom cell.
  • Confusing a column’s last row with the sheet’s last row: A result for column A says nothing about records that appear only in other columns. Pick the column that defines a complete record, or search across the sheet when that is actually the requirement.

Filtered or hidden rows are still part of the worksheet’s cell contents; these methods do not limit the search to visible rows. A filter hiding a record therefore does not, by itself, make that row disappear from the last-row calculation.

Using the result in a range or loop

Once lastRow is set, use it to create a range bounded by the actual data rather than processing every worksheet row. For example, ws.Range("A2:A" & lastRow) describes column A from row 2 through the detected row. If row 1 is a header, starting at row 2 excludes it. Before building such a range, check that lastRow is at least 2; otherwise the intended data range may be empty or invalid for the task.

A loop can also use the value as its endpoint. Remember that a last row is an absolute worksheet row number, not a count of data records. If data starts on row 2 and ends on row 21, there are 20 row positions in that span, including any blank gaps. If you need the number of nonblank cells rather than the final row number, use a counting approach instead.

Adding and running the macro

In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, then choose Insert > Module and paste the procedure into the module. If the Developer tab is visible, Developer > Visual Basic also opens the editor. Run a selected macro from the editor with F5, or return to Excel and use Developer > Macros.

On Mac, use Developer > Visual Basic if the Developer tab is available, or the Visual Basic Editor command under the Tools > Macro menu in Excel. Keyboard shortcuts can vary with the Mac keyboard and function-key settings, so the menu command is a dependable alternative to relying on a Windows shortcut. Excel for the web can open a workbook containing VBA, but it cannot run or edit VBA macros; use the desktop application.

Only enable macros in workbooks you trust. A macro that merely reads a row number is simple, but VBA code can also change or delete workbook content. Keep a backup before running code, particularly when adapting an example to perform edits.

Frequently Asked Questions

How do I find the last used row in column B? Replace "A" with "B" in the cell reference. For example, use ws.Cells(ws.Rows.Count, "B").End(xlUp).Row, with ws set to the worksheet you want to search.

Why does my code return 1 when the column is blank? End(xlUp) reaches the top cell of an empty column, so its row is 1. Check whether the column has contents first, or use Find and test whether the returned range is Nothing.

Does End(xlUp) skip blank rows? It returns the lowest occupied cell in the column when started from a blank bottom cell, so blank rows between entries do not prevent it from finding a lower entry. It does not return the first blank row after a continuous list.

Will it count a formula that displays nothing? Yes, the End method treats a cell containing a formula as occupied even when the displayed result is an empty string. The Find example using LookIn:=xlFormulas does too. Decide whether formula presence or visible values define your last row.

How do I find the last row used anywhere on the sheet? That is different from finding the last row in one column. Search the full worksheet range rather than a single column, and define whether any content in any column should count. If records rely on a particular key column, searching that column is usually more meaningful.

Related

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.