Excel VBA: Find the Last Row of Data in a Column Easily

To find the last populated row in column A with Excel VBA, use Cells(Rows.Count, "A").End(xlUp).Row, with the worksheet explicitly specified. If the column might be completely empty, check for that case first: the basic expression returns row 1 even when there is no data.

The quick VBA answer

Excel has 1,048,576 rows per worksheet, so VBA can start at the bottom of the target column and move upward to the next non-empty cell. For example, with data in column A on a worksheet named Data, the familiar approach is:

Dim ws As Worksheet
Dim lastRow As Long

Set ws = ThisWorkbook.Worksheets("Data")
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

If the last non-empty cell in A is A247, lastRow is 247. The row number is a Long because worksheet row numbers can exceed the range of the older Integer data type. The expression looks for the last occupied cell in that column; it does not decide whether a row is logically part of a particular table or record set.

Using ws matters. An unqualified Cells or Rows.Count can refer to the active worksheet, which may not be the sheet your macro intends to process. Qualifying the references makes the result predictable even if another sheet is selected.

How End(xlUp) finds the row

End(xlUp) is the VBA counterpart to navigating upward from a cell with Excel’s End-and-arrow-key behavior. The expression first selects the bottom cell in the specified column, then moves up to the edge of the relevant non-empty region. When the bottom of the sheet is blank, that destination is ordinarily the last cell in the column that contains a value or formula.

Starting at the bottom is safer than starting at A1 and using End(xlDown). If A1 is filled but A2 is blank, End(xlDown) stops at the edge of the first block rather than discovering data further down. Starting at the last worksheet row and moving upward can pass over blank rows between entries, so gaps in the data do not normally prevent it from locating the last occupied cell.

There is one unusual boundary case: if the very last row of the worksheet is occupied, starting from that occupied bottom cell can make End(xlUp) travel to the top edge of its contiguous block. The usual shortcut assumes the sheet’s final row is empty. If a workbook could genuinely use row 1,048,576, use the Find-based method below or add a check for that boundary.

Handle a completely blank column

When a column is empty, the basic End expression returns 1: moving up from the bottom of an empty column reaches its top. That result can be mistaken for a real record. Decide what your macro should return for no data and test for it rather than treating row 1 as proof that a value exists.

A simple option is to use Application.CountA on the target column before calling End. CountA counts cells containing values and formulas, including formulas whose displayed result is an empty string. For a large, frequently processed workbook, a Find search can avoid a separate full-column count and return Nothing when no matching cell exists.

Also distinguish an empty column from a header-only column. If A1 contains a heading and A2 downward is empty, the last occupied row is 1, but the last data row beneath the header is effectively 0. In code that processes records, compare the result with the header row before starting a loop.

Use Find for a robust last-cell search

Excel’s Find method can search the entire target column from the bottom upward. It is useful when you want a clear empty result and when you want to avoid the bottom-row edge case associated with End(xlUp). Specify the search settings: Excel remembers some Find options from earlier searches, and omitted arguments can inherit those settings.

Option Explicit

Sub ShowLastUsedRowInColumnA()
    Dim ws As Worksheet
    Dim lastCell As Range
    Dim lastRow As Long

    Set ws = ThisWorkbook.Worksheets("Data")

    ' Search formulas and constants, from the bottom of column A.
    Set lastCell = ws.Columns("A").Find( _
        What:="*", _
        After:=ws.Cells(1, "A"), _
        LookIn:=xlFormulas, _
        LookAt:=xlPart, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, _
        MatchCase:=False)

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

    Debug.Print lastRow
End Sub

In this example, an empty column prints 0 to the Immediate window; if A247 is the last matching cell, it prints 247. In the Visual Basic Editor, the Immediate window can be shown with Ctrl+G on Windows. The search uses xlFormulas, so a cell containing a formula is treated as occupied even if its displayed result looks blank. This is often appropriate when the question is where the column’s stored contents end.

Before running any macro, save a copy of the workbook. VBA changes cannot be undone with Ctrl+Z. If you adapt the example to change or delete cells, test it on a copy first.

Choose what counts as the end of data

“Last row” can mean different things depending on the task. End(xlUp) and the Find example locate occupied cells, but that may not match the boundaries of the records you intend to process.

Last cell with a formula or constant: Use End(xlUp) or Find with LookIn:=xlFormulas. A formula returning an empty string still counts as content.
Last cell with a displayed value: Consider Find with LookIn:=xlValues. A formula that displays an empty string may not be treated as a visible value, so confirm the behavior matches your worksheet.
Last row with a required field: Search the column that defines a valid record, such as an ID column, rather than a notes or optional-field column.
Last row in a fixed data block: Use the block or table’s defined boundary instead of assuming every used cell in a worksheet belongs to that dataset.

For example, suppose column A contains customer IDs through row 50, while column B contains occasional notes through row 65. The last occupied row in B is 65, but the last customer record may still be row 50. A macro that loops through customer records should use the ID column or the table boundary.

End(xlUp), Find, and UsedRange compared

End(xlUp) is compact and easy to read. It is a good default when the target column has a known role, the final worksheet row is not occupied, and returning row 1 for a blank column is either acceptable or handled separately.

Find takes more code but supports an explicit search direction and a Nothing result for no match. It also lets you choose whether to search formulas or displayed values. Providing its options explicitly reduces surprises from remembered Find settings. Use a worksheet-qualified range, as in the example, so the search cannot accidentally run on a different active sheet.

UsedRange describes the worksheet’s used area, not necessarily the meaningful end of one particular column. Formatting or previously used cells can make the reported area extend beyond current records. It can be helpful for operations on a whole sheet, but it is not automatically the best answer to “what is the last data row in column A?”

Common mistakes and edge cases

Using End(xlDown) from the first row: A blank cell or gap can stop the navigation early. Start at the bottom and move up when finding the last entry.
Forgetting to qualify the worksheet: A macro may return a result from whichever sheet happens to be active. Use ws.Cells and ws.Rows.Count.
Assuming row 1 always means data exists: It can mean the column is empty. Define an explicit no-data result or test for a header-only sheet.
Confusing formulas with visible values: A formula that displays an empty string is still a formula in the cell. Choose formula-based or displayed-value-based searching according to the task.
Assuming hidden rows are excluded: These methods locate occupied cells even if their rows are hidden or filtered out. A filter changes what is shown, not whether those cells contain data.
Using the last cell in any column as the record boundary: Optional comments, formatting, or stray entries can extend beyond the actual records. Pick a reliable key column or table.

Formatting alone is another source of confusion. A cell that has only a fill color or border is not the same as a cell containing a value or formula for the End(xlUp) test. However, formatting can affect a worksheet’s reported used area, which is one reason UsedRange and last-data-row searches may disagree.

Find the last row in another column or the last column

To search a different column, replace the column reference consistently. For example, use "C" in place of "A" in both the column range and the starting cell when using the Find pattern. With End(xlUp), change the column argument to "C". Keep the worksheet reference in place.

To find the last occupied column in a particular row, reverse the direction: start at the worksheet’s far-right column and use End(xlToLeft). This answers a different question from finding the final data row, and an entirely blank row has a similar edge case. For both directions, define what blank and header-only data should mean before using the result in a loop or range resize.

Frequently Asked Questions

What is the shortest VBA code to find the last row in column A?

With the intended worksheet referenced by ws, use lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row. Add a blank-column check if row 1 would be an incorrect result for your task.

Does End(xlUp) skip blank rows?

Yes. Starting at the bottom of the worksheet and moving upward can find the last occupied cell even when there are empty cells between records. It does not mean every row between the first and last entry contains data, so a processing loop may still need to test each row.

Will it find formulas that display nothing?

End(xlUp) treats a formula cell as occupied even when the formula displays an empty string. The Find example configured with LookIn:=xlFormulas does the same. If you only want cells with visible results, use a values-based search and verify that its treatment of empty-string formulas is right for the workbook.

Does filtering change the last row result?

No. A hidden or filtered-out row can still contain a value or formula, and the ordinary End and Find searches can include it. If the macro should process only visible rows, that is a separate filtering requirement from finding the last occupied row.

Why does my result include a row below the records?

The chosen column may contain a formula, a stray value, or an optional entry farther down than the main dataset. Check the cells below the expected records and consider searching a required ID column or using an Excel table’s data boundary instead.

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.