To get the last nonempty row in a particular column, start at the bottom of that worksheet column and move upward to the next occupied cell: lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row. Use this when column A reliably identifies your records; use Excel’s Find method instead when you need the last occupied row anywhere on the sheet.
The Basic End(xlUp) Method
End(xlUp) is the VBA equivalent of starting in a cell and pressing Ctrl+Up to move to the next occupied cell above it. In the statement above, ws.Rows.Count supplies the final row number on that worksheet, so the code starts at the bottom of column A and searches upward. The returned Row value is a number, such as 25, rather than a cell address.
Here is a complete example for a worksheet named Data. It reports zero if column A is truly empty, because the basic upward search otherwise returns row 1 for an empty column.
Sub ShowLastRowInColumnA()
Dim ws As Worksheet
Dim lastRow As Long
' ThisWorkbook is the workbook containing this code.
Set ws = ThisWorkbook.Worksheets("Data")
' Find the last occupied cell in column A.
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
' End(xlUp) returns 1 when the entire column is empty.
If lastRow = 1 And IsEmpty(ws.Cells(1, "A").Value) Then
lastRow = 0
End If
MsgBox lastRow
End Sub
Replace "Data" with the worksheet’s tab name and "A" with the column that marks a valid record. The check uses IsEmpty, so a formula in A1 that displays an empty string is still treated as a cell containing a formula, not as a truly empty cell.
Choose the Column That Defines a Record
The basic method answers a specific question: “What is the last occupied row in this column?” It does not necessarily identify the final row containing information in every column. For example, if column A holds optional notes and column C holds a required order number, column A may stop at row 40 while the records in column C continue to row 75. Searching A would return the wrong boundary for the order list.
Pick a column that should be filled for every record, such as an ID, date, or name. If no single column is consistently populated, use a whole-sheet search instead. Be explicit about whether you want the last row in a particular column, the last row containing anything on the worksheet, or the last row in a structured table. Those are different questions and can have different answers.
- Use a key or required field when you want to find the end of a data list.
- Use a chosen output column when a macro should append new values there.
- Use a whole-sheet search when records can have blanks in any one column.
- Use a table’s data body when the table, rather than the worksheet, defines the records.
Find the Last Occupied Row Anywhere on a Sheet
When data can appear in any column, the Find method can search the worksheet by rows and return the last cell it finds. The following code looks for values or formulas and safely handles a sheet with no matching cells.
Sub ShowLastOccupiedRow()
Dim ws As Worksheet
Dim lastCell As Range
Dim lastRow As Long
Set ws = ThisWorkbook.Worksheets("Data")
' Search all cells, by rows, starting from the end.
Set lastCell = ws.Cells.Find(What:="*", _
After:=ws.Cells(1, 1), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False, _
SearchFormat:=False)
If lastCell Is Nothing Then
lastRow = 0
Else
lastRow = lastCell.Row
End If
MsgBox lastRow
End Sub
Specifying the search options matters. Excel remembers several settings from earlier Find operations, so leaving options implicit can make VBA behave differently depending on a previous search. Here, SearchOrder:=xlByRows asks Excel to consider row order, while SearchDirection:=xlPrevious finds the last matching cell in that order. LookIn:=xlFormulas includes formulas even when their displayed result looks blank. If you want to search displayed values instead, use xlValues, while keeping in mind that formulas returning an empty string may then be treated differently.
What Counts as the Last Row?
Excel does not have just one universal “last row.” A cell can be occupied because it contains a constant, a formula, or—in some methods—formatting or other worksheet history. A formula such as one that displays an empty string is still a formula. It can therefore count as occupied when the search looks in formulas, even though a person sees a blank cell.
Blank rows inside a list do not stop End(xlUp) from finding a later occupied cell. If A10 and A15 contain data but A11:A14 are blank, searching upward from the bottom of column A returns row 15. That is useful for locating the last record, but it does not prove that every row between the first and last records contains data. If your macro requires a continuous list, validate the gaps separately.
Formatting is another source of confusion. A cell may look empty but have a border, fill, or other formatting. The simple End(xlUp) approach generally focuses on cell contents in the chosen column, whereas worksheet-range properties can be affected by formatting. Decide whether “last” means last value or formula, last displayed value, last formatted cell, or last record according to your task.
Why UsedRange Can Give a Surprising Answer
UsedRange returns the worksheet’s used range, but its boundary is not always the same as the last row with meaningful data. Formatting applied far down a sheet, or content that was later deleted, can leave the used range extending beyond the visible list. For that reason, ws.UsedRange.Rows.Count by itself is not a reliable replacement for the last-row search: the used range may also start below row 1.
If you specifically need the ending row of the range Excel currently reports as used, calculate it by adding the range’s starting row to its row count, then subtracting one. For locating actual records, however, a key-column search or the whole-sheet Find example is usually clearer. Avoid deleting data or formatting just to make a row-detection macro work unless changing the worksheet is an intentional part of the task.
Getting the Last Row in an Excel Table
If the records are in an Excel table, use the table’s data body rather than guessing from worksheet rows. A table has a defined data area that expands as records are added. In VBA, its DataBodyRange represents the data rows and is Nothing when the table has no data rows. The header is not part of that body, so an empty table does not mistakenly report its header as a record.
Sub ShowTableRowCountAndLastRow()
Dim ws As Worksheet
Dim tbl As ListObject
Dim lastRow As Long
Set ws = ThisWorkbook.Worksheets("Data")
Set tbl = ws.ListObjects("Table1")
If tbl.DataBodyRange Is Nothing Then
lastRow = 0
Else
lastRow = tbl.DataBodyRange.Row + _
tbl.DataBodyRange.Rows.Count - 1
End If
MsgBox lastRow
End Sub
This example returns the worksheet row number of the table’s last data row, not simply the number of records. If you only need the number of table records, use tbl.ListRows.Count. Replace "Table1" with the table’s actual name; select a cell in the table and check the Table Design tab in Excel to see its name.
Common Errors and How to Avoid Them
- Using the active sheet accidentally: Unqualified
Cells,Rows, orRangereferences can act on whichever sheet is active. Assign a worksheet to a variable and qualify the references, as in the examples. - Using the wrong workbook:
ThisWorkbookmeans the workbook that contains the VBA code. If the macro is stored in Personal.xlsb or an add-in, that may not be the workbook with the data. Set a worksheet from the intended workbook explicitly in that situation. - Getting 1 for an empty column: A search upward from the bottom lands at row 1 if there is no earlier occupied cell. Add an emptiness check when your program needs zero to mean “no records.”
- Counting a formula that looks blank: A formula returning an empty string is not the same as an unused cell. Decide whether formulas should count and choose the search method and
LookInsetting accordingly. - Assuming the last row means the last record: Notes, totals, or stray content below the list may extend the worksheet’s last occupied row. Search a required record column or the table itself if those extra cells should not define the list boundary.
VBA works in the desktop Excel app for Windows and Mac, although some unrelated macro features differ between platforms. Excel for the web does not create or run VBA macros; open the workbook in desktop Excel to use this code. Before running a macro, save a copy of the workbook: macro actions cannot be undone with Ctrl+Z. If you store the code in the workbook, use a macro-enabled file format such as .xlsm to retain it.
Which Method Should You Use?
For a regular list with one dependable key column, End(xlUp) is short, fast, and easy to understand. For scattered data where any column might hold the final entry, use Find with explicit options and test whether it returned Nothing. For a formatted Excel table, use its DataBodyRange or row collection. Reserve UsedRange for tasks that genuinely concern Excel’s reported used area, not as a shortcut for finding the final meaningful record.
Before using the result to append data, consider what should happen when there are no records, whether a header occupies row 1, and whether formulas that look blank count. For example, if row 1 is a header and column A has no records, the basic method will return 1; that may be correct as the last occupied row, but a macro that needs the next record row should treat the header-only case separately. Clear rules at this boundary prevent accidental overwrites and gaps in automated lists.
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, with ws set to the intended worksheet. Add an empty-column check if you want an empty column to return zero rather than row 1.
How do I find the last used row in any column?
Use ws.Cells.Find with a wildcard, SearchOrder:=xlByRows, and SearchDirection:=xlPrevious. Check whether the returned range is Nothing before reading its row number.
Does End(xlUp) stop at a blank row?
No. Starting from the bottom of a column, it moves upward to an occupied cell, so blank rows between entries do not prevent it from finding a later entry.
Why does my macro return 1 when the column is empty?
The upward search reaches the top cell, A1, when it finds no occupied cell above. Test whether that cell is empty and set the result to zero if that is the convention your macro needs.
Can I use VBA in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel for Windows or Mac to run them.
Related
- Excel Shortcut to Go to a Specific Column in Seconds
- Excel Shortcut to Insert a Cut Row
- Excel Shortcut to Insert Another Row in Windows and Mac
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA AutoFilter Array: Filter Multiple ValuesTo filter a column for several exact values with Excel VBA, pass an array to Criteria1 and set…
- Excel VBA Find LookIn: What It Means and How to Use ItIn Excel VBA, LookIn is an argument of the Range.Find method that tells Excel what kind of content…
- Excel VBA Arrays: What They Contain and How to Use ThemAn Excel VBA array contains an ordered set of elements that a macro can access by index; those…
- Clear Contents Macro Excel: VBA Code and Safe Use GuideIn Excel, a clear-contents macro removes cell values and formulas while leaving cell formatting in place. In Microsoft…
- Excel VBA Find a Substring in a StringIn Excel VBA, use InStr to find a substring inside a string: it returns the position of the…
- Filter an Excel PivotTable With VBA: Fields, Items, and CodeTo filter an Excel PivotTable with VBA, reference the PivotTable and field, clear any existing filter, then set…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.