In Excel VBA, use ws.Cells(ws.Rows.Count, "A").End(xlUp).Row to find the last nonempty cell in column A. If you need the last populated row anywhere on a worksheet, use the Find method instead; the right choice depends on what “populated” means in your data.
What counts as the last populated row?
A worksheet can have different answers to this question depending on what you are measuring. The last row containing a value in a particular column is not necessarily the last row containing anything anywhere on the sheet. For example, column A might stop at row 25 while a note in column F extends to row 40.
It also matters whether a formula that displays an empty string counts as populated. A cell containing =IF(B2="","",B2) contains a formula even when its displayed result is blank. A cell with a space character is also not truly empty. Decide whether your macro should detect stored content, visible results, or just the records in a specific field before choosing a method.
Find the last row in one column with End
The End(xlUp) pattern is compact and fast for a defined data column. It starts at the worksheet’s bottom row and moves upward to the first nonempty cell it encounters. The following complete macro checks column A on a worksheet named Data and returns zero if that column has no content.
Option Explicit
Sub ShowLastRowInColumnA()
Dim ws As Worksheet
Dim lastRow As Long
Set ws = ThisWorkbook.Worksheets("Data")
' Return zero when column A contains no values or formulas.
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 populated row in column A: " & lastRow
End Sub
Change "Data" and "A" to match the worksheet and column you actually use. ThisWorkbook means the workbook containing the macro. If the code is stored in a different workbook, such as the Personal Macro Workbook, explicitly refer to the workbook containing the data rather than assuming ThisWorkbook is the target.
The empty-column check is important: if column A has no content, the upward navigation can still end at row 1, which may look like a real result. Returning zero makes “no populated row” unambiguous. If row 1 is a header and the data must begin below it, you can also treat a result of 1 as “no data rows.”
Find the last populated row anywhere on a sheet
When content may appear in any column, searching the worksheet is more appropriate than checking column A. This macro searches by rows, beginning at the bottom of the search order, and reports zero if no matching cell is found. Using LookIn:=xlFormulas means formulas count even when their displayed result is an empty string.
Option Explicit
Sub ShowLastUsedRowOnSheet()
Dim ws As Worksheet
Dim foundCell As Range
Dim lastRow As Long
Set ws = ThisWorkbook.Worksheets("Data")
' Search cell contents and formulas, not formatting alone.
Set foundCell = ws.Cells.Find(What:="*", _
After:=ws.Cells(1, 1), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False)
If foundCell Is Nothing Then
lastRow = 0
Else
lastRow = foundCell.Row
End If
MsgBox "Last populated row on the sheet: " & lastRow
End Sub
Excel remembers several Find-dialog settings between searches. Supplying the important arguments in the code avoids relying on settings left by a previous manual search or macro. SearchOrder:=xlByRows asks for the last matching row; xlPrevious searches backward. The result is the row of the last matching cell, not necessarily the last row of a neatly shaped table.
Decide how formulas and blank-looking cells should count
The choice between xlFormulas and xlValues changes what the search considers a match. With xlFormulas, a formula is treated as content even if it currently displays nothing. This is useful when formulas are part of the worksheet’s structure and should be included in the last-row boundary.
Use xlValues when the goal is to find cells with results rather than formula text. This can be useful when formulas returning empty strings should not extend the apparent data. However, a result-based search may not match your definition of a record in every workbook. Test it with representative cells, including numeric values, text, errors, and formulas returning an empty string. Formatting such as a fill color or border alone is not cell content and should not be used to define a populated row.
For a column-specific search, the same distinction applies. End(xlUp) treats a formula cell as nonempty even when its result looks blank. If formulas continue far below the real records, the returned row will include them. If you need to ignore those formulas, use a values-based search over the relevant column and verify the behavior against your workbook’s data.
Use a table when the data is an Excel table
If the records are stored in an Excel table, the table itself often provides a clearer boundary than searching a whole worksheet. In VBA, a table is a ListObject. Its DataBodyRange represents the data rows without the header. If the table has no data rows, DataBodyRange is Nothing, so check for that before asking for its row count.
A table is especially useful when records have multiple columns and users add rows over time. It expands as data is entered directly below it, and formulas or formatting can be managed as table columns. Choose a specific table column when you want the last record with an entry in that field; use the table’s overall data range when any populated field should qualify. Do not assume a worksheet’s last used row and a table’s last data row are identical: notes, totals, or other content may sit outside the table.
Choose the method that matches the task
- One reliable key column: Use
End(xlUp)when every valid record has a value in that column, such as an order ID in column A. - Content anywhere on the worksheet: Use
Findwith a row search when data can extend beyond the key column. - A defined table: Inspect the table’s data range when the records already belong to an Excel table.
- Displayed results only: Consider a values-based search, but confirm how it treats formulas that return an empty string in the workbook.
For example, if column A contains IDs in rows 2 through 25 and column D has a note in row 30, End(xlUp) on column A returns 25. A worksheet-wide search returns 30 if that note is cell content. Neither answer is inherently wrong: the first answers “last row with an ID,” while the second answers “last row containing matching content anywhere.”
Common problems and how to avoid them
Gaps in the data: A blank row between records does not stop End(xlUp) from finding the last nonempty cell above it, because the search starts at the bottom of the column. But a blank in the key column means that column no longer reliably identifies every record. Use another dependable field or search the full data area if records can lack that key.
Headers and empty sheets: A header in row 1 makes the last nonempty row 1 even when there are no records. Decide whether your macro should return the header row, zero, or a separate “no data” result. Add that rule explicitly rather than assuming row 1 always represents a record.
Formulas copied far down: A formula-filled column can make the last row much larger than the last visible record. This is not a VBA error: those cells contain formulas. Choose whether the macro should include formula cells or only meaningful results, and avoid placing permanent formulas far below the intended data if they should not count.
Spaces and invisible characters: A cell containing one or more spaces is populated from Excel’s perspective. If imported data may contain spaces, clean or validate it as a separate step. A last-row routine finds cell content; it does not decide whether that content is a valid record.
Formatting mistaken for data: Formatting can make Excel’s reported used area appear larger, but formatting alone is not the same as a populated cell. The examples above search content or formulas. Avoid using UsedRange as a guaranteed answer to “last row with data”: it can include cells affected by prior editing or formatting, and its boundary may not match the records you intend to process.
Run and adapt the macro safely
In desktop Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11. On Mac, the editor shortcut is commonly Option+F11; some keyboards require Fn as well, and the menu route is Tools > Macro > Visual Basic Editor. In the editor, choose Insert > Module, paste a complete procedure, and change the worksheet name and column to suit the workbook. Excel for the web does not run VBA macros; use desktop Excel to run this code.
Save a copy of the workbook before running a macro. These examples only display a row number, but macros that act on the result may edit or delete cells, and macro actions cannot be undone with Ctrl+Z. Once you have the row number, qualify later references with the worksheet, for example by using ws.Cells(lastRow, "A"), rather than relying on whichever sheet happens to be active.
Frequently Asked Questions
How do I find the last row in column A in VBA?
Use ws.Cells(ws.Rows.Count, "A").End(xlUp).Row. Add an empty-column check if a completely blank column should return zero rather than row 1.
How do I find the last row with data in any column?
Use ws.Cells.Find with SearchOrder:=xlByRows and SearchDirection:=xlPrevious, then check whether the result is Nothing. This searches beyond a single designated column.
Does End(xlUp) count formulas that display blank?
Yes. A cell containing a formula is not empty to End(xlUp), even when the formula displays an empty string. A formula-filled area can therefore produce a later row than the visible records.
Why does my last-row macro return 1 on an empty column?
When there is no nonempty cell above, upward navigation can land on the first row. Check the column with CountA before using the result, and define what your procedure should return when there is no data.
Is UsedRange the best way to find the last populated row?
Not usually when you specifically need the last row with content. UsedRange describes Excel’s used area, which can be influenced by prior edits or formatting. Use a column search, worksheet-wide Find, or a table boundary that matches the data you mean to process.
Related
- Excel Shortcut to Select to the Bottom of a Column
- Excel Shortcut to Select All Rows in a Column at Once
- Excel Data Validation from a List: Setup and Troubleshooting
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Label Not Defined: Fix the Compile ErrorExcel VBA’s Label not defined compile error means a procedure refers to a jump target that VBA cannot…
- Excel VBA: Return the Last Used Row Number ReliablyIn Excel VBA, the usual way to get the last row in a particular column is to start…
- VBA Excel Offset: How to Select a Range ReliablyIn Excel VBA, use the Offset property on a Range to refer to cells a chosen number of…
- Using VBA to Sort Contracts in Excel: A Practical LessonTo sort a contract register by expiration date with VBA, put the records in one rectangular range with…
- VBA AutoFilter for Tanaka: Filter Excel Rows by NameTo show only rows whose name is Tanaka, apply Excel’s AutoFilter to a range that includes its header…
- How to Copy and Paste Using VBA in Excel: Macro GuideTo copy a range in Excel with VBA, use Range.Copy Destination:=...; for values only, assign one range’s .Value…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.