Excel VBA: Return the Last Used Row Number Reliably

VBA & macros · Updated November 19, 2026

In Excel VBA, the usual way to get the last row in a particular column is to start at the bottom of that column and use End(xlUp). For the last used row anywhere on a worksheet, use Range.Find instead; the right choice depends on whether you mean the last entry in one column or the last entry across the sheet.

Get the last row in a specific column

This macro finds the last non-empty cell in column A on a worksheet named Data and prints its row number in the Immediate window. Replace the worksheet name or column letter to suit your workbook.

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

    'Set the worksheet whose data you want to check.
    Set ws = ThisWorkbook.Worksheets("Data")

    'Start at the bottom of column A and move up to its last non-empty cell.
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    Debug.Print lastRow
End Sub

Before running a macro, save a copy of the workbook: VBA macros cannot be undone with Ctrl+Z. To run this example, open the VBA editor, insert the code in a standard module, and run ShowLastRowInColumnA. The Immediate window is available in the editor; Debug.Print reports the number there rather than displaying a message in the worksheet.

The key expression is ws.Cells(ws.Rows.Count, "A").End(xlUp).Row. ws.Rows.Count supplies the worksheet’s bottom row, so the code works across Excel versions without hard-coding a row limit. Starting from the bottom also means that blank cells between entries do not stop the search.

What “last row” means

There is not one universal last-row number. A worksheet can have data in column A through row 50 and in column D through row 80. The last row in A is 50, while the last row containing something anywhere on the sheet is 80. Decide which question your code needs to answer before choosing a method.

  • Last row in a key column: Check a column that should contain an entry for every record, such as an ID column.
  • Last row in a particular field: Check that field’s column, even if other columns extend farther down.
  • Last row anywhere on the sheet: Search all cells, not just the first column.
  • Last row in a table: Use the table’s data range, which is often clearer than searching worksheet cells.

Choosing the wrong definition can quietly omit records or include unrelated material. For example, if column A has customer IDs through row 50 but column F contains notes through row 60, using A to find the last used row is correct for processing customers only if those later notes are irrelevant.

How End(xlUp) works

In Excel, pressing End and then the Up Arrow moves to the edge of a data region. VBA expresses that movement as End(xlUp). Starting at the final worksheet row in a chosen column and moving upward lands on that column’s last non-empty cell. The Row property returns its numeric worksheet row, not the number of populated records.

This method is efficient and easy to read, making it a good default when one column reliably identifies the records. Change the column argument to check another field. For example, use "C" instead of "A" to find the last non-empty cell in column C. Keep the worksheet qualified as ws.Cells; unqualified references can accidentally use whichever sheet is active when the code runs.

A significant edge case is a completely empty column. Starting at its bottom and moving up reaches row 1, so the expression returns 1 even though there is no data. If row 1 is a header, it also returns 1 when the header is the only non-empty cell. That result can be useful when a header-only sheet should have zero data records, but it is not the same as “no cell found.” Add a check for the expected header or data before treating the result as a record count.

Find the last used row anywhere on a sheet

To search the entire worksheet rather than a single column, use Find with a reverse row search. This function returns zero for a completely empty sheet and otherwise returns the row number of the last cell found.

Function LastUsedRow(ByVal ws As Worksheet) As Long
    Dim lastCell As Range

    'Search the sheet from the bottom-right toward the first row.
    Set lastCell = ws.Cells.Find(What:="*", _
        After:=ws.Cells(1, 1), _
        LookIn:=xlFormulas, _
        LookAt:=xlPart, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, _
        MatchCase:=False, _
        SearchFormat:=False)

    'Return zero when Find does not locate a cell.
    If lastCell Is Nothing Then
        LastUsedRow = 0
    Else
        LastUsedRow = lastCell.Row
    End If
End Function

Place this function in a standard module. A calling macro can use LastUsedRow(ThisWorkbook.Worksheets("Data")) and store the returned value in a Long variable. In VBA, Long is the appropriate integer type for worksheet row numbers; an Integer cannot represent all rows in a modern Excel worksheet.

The search settings are written out deliberately. Excel’s Find dialog remembers some settings from earlier searches, so relying on defaults can make VBA behave differently depending on what a person searched for previously. Specifying the search order, direction, and other options makes the result more predictable. With LookIn:=xlFormulas, cells containing formulas count as found even if a formula displays an empty string. Use this whole-sheet method when formulas or data in columns beyond your chosen key column should be included.

Formulas, blank-looking cells, and gaps

A cell can look blank without actually being empty. A formula such as one that returns an empty string still leaves a formula in the cell. A cell containing spaces is also not empty. As a result, a last-row search may report a row whose visible value appears blank. The Find example explicitly searches formulas, so formula cells count; End(xlUp) also treats a cell containing a formula as occupied.

Decide whether those cells should count for your task. If you only want rows with a meaningful ID, search the ID column rather than a presentation or calculation column that contains formulas down hundreds of rows. If formulas are meant to define the used extent, counting them is appropriate. Avoid assuming that a visually blank cell will be ignored by every VBA method.

Gaps inside a column are different from blank-looking cells at the bottom. If A2, A3, and A8 contain values, starting at the bottom of column A and using End(xlUp) returns row 8. It does not stop at the first gap. But if A8 is blank and A2:A3 are the only entries, it returns row 3, as expected. To count records rather than find the bottom row, use a separate counting method and define how blank or formula-based records should be handled.

Use a table when the data is an Excel Table

If the records are stored in an Excel Table, its object model can express the intent more clearly than a worksheet-wide search. A table is represented in VBA by a ListObject. Its DataBodyRange refers to the data rows without the header. When the table has data, the row of the final data cell gives the worksheet row number.

There is an important empty-table case: DataBodyRange can be Nothing when the table has no data rows. Check for that before asking for its row. Also distinguish a worksheet row number from the number of records in the table: a table beginning on row 5 with 10 data rows ends on worksheet row 14, not row 10. Tables automatically expand as records are added, which can make them convenient for repeated imports or reports.

Choose the method that matches the task

  • Use End(xlUp) when a specific column reliably contains every record and you want a compact, fast answer.
  • Use Find when you need the last occupied row across multiple columns or want a clear zero result for an empty sheet.
  • Use a ListObject when the data is already an Excel Table and the question concerns that table’s records.
  • Use a count instead when you need the number of qualifying entries, not the worksheet row position of the last one.

For example, an import routine that appends customer records should usually find the end of a required ID column, then write to the next row. A report that scans formulas and notes across several columns may need the whole-sheet Find approach. A table-based workflow should usually use the table’s own range, so a note below the table does not accidentally extend the data boundary.

Common mistakes and checks

  • Checking the wrong column: A blank in the chosen column can make the reported row too small compared with other fields. Choose a dependable key column or search the whole sheet.
  • Assuming row 1 means data exists: An empty column can return row 1 with End(xlUp). Handle an empty sheet or header-only sheet explicitly.
  • Using ActiveSheet accidentally: A macro that changes the active worksheet can return a plausible but incorrect result. Qualify references with the intended worksheet variable.
  • Confusing used formatting with data: Formatting applied far down a sheet can make Excel’s apparent used range extend beyond meaningful records. A value-based search or a known key column is usually a better fit for finding data.
  • Using Integer for row numbers: Use Long for row indexes to avoid an overflow on worksheets with many rows.

For a quick visual check on Windows, Ctrl+End moves to Excel’s last used cell, but that location is not necessarily the last row of meaningful data: formatting or old content may extend the used area. In Excel for Mac, Control+End serves the corresponding navigation purpose, though some keyboards may require the Fn key. These shortcuts help inspect a worksheet; they do not replace choosing the right VBA definition of “last row.”

Excel for Mac and Excel for the web

The VBA patterns shown here are intended for desktop Excel for Microsoft 365 on Windows. Excel for Mac supports VBA, though macro availability and some object-model behavior can differ by version. The worksheet references and basic End and Find techniques are common desktop VBA patterns; verify a macro in the specific Mac environment if it interacts with other features.

Excel for the web does not run VBA macros. You can open a workbook that contains VBA in the browser, but you need desktop Excel to run the code. If the workflow must run in a browser, consider Office Scripts instead; they use TypeScript and are a different automation model, not VBA code that can be pasted into the VBA editor.

Frequently Asked Questions

How do I get the last row in column A in VBA?
Use ws.Cells(ws.Rows.Count, "A").End(xlUp).Row, where ws is the worksheet you want to check. It returns 1 for an empty column, so account for that if zero is your required empty result.

How do I find the last row on the entire worksheet?
Use ws.Cells.Find with SearchOrder:=xlByRows and SearchDirection:=xlPrevious. Check whether the returned range is Nothing; if it is, the sheet contains no found cells.

Does End(xlUp) work if there are blank rows in the data?
Yes. It starts at the bottom of the selected column and moves upward to the last non-empty cell, so blank rows above that cell do not stop it. It cannot find records that have no entry in the column you chose.

Why does VBA return a row that looks empty?
The cell may contain a formula returning an empty string, spaces, or other content that is not visibly apparent. Check the formula bar or choose a key column whose contents match the definition of a record.

Why does the last-row code return 1 when the sheet is empty?
The common End(xlUp) expression reaches row 1 when every cell in the checked column is empty. Use a separate check for an empty column or use a whole-sheet Find routine that returns zero when no cell is found.

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.