To get the last data row of an Excel table (a VBA ListObject), use its DataBodyRange: the last worksheet row is tbl.DataBodyRange.Row + tbl.DataBodyRange.Rows.Count - 1. Check whether DataBodyRange is Nothing first, because a table with no data rows has no body range.
What “last row of a table” means
Excel uses “table” to mean a structured range created with Insert > Table or Home > Format as Table. In VBA, that object is a ListObject. It has a header row, a body containing data rows, and optionally a totals row. These parts matter because “last row” can mean different things: the final data record, the table’s totals row, or the last occupied row anywhere on the worksheet.
For example, suppose a table’s header is on worksheet row 4 and it has six records beneath it, on rows 5 through 10. Its last data row is worksheet row 10. The table has six data rows, but ListRows.Count returns 6, not the worksheet row number 10. If code needs to address cells on the sheet, use the row number from the range. If it only needs the number of records, use the row count.
A structured table is usually the best starting point when the question specifically concerns a table. Its range grows as table records are added, so code can follow the table rather than guessing where a block of data ends.
Recommended method: use DataBodyRange
Set a reference to the worksheet and table, test whether there are any data rows, and calculate the final row from the body range. Replace the example worksheet and table names with the names in your workbook. Table names appear on the Table Design tab when a cell in the table is selected.
Sub ShowLastDataRowOfTable()
Dim ws As Worksheet
Dim tbl As ListObject
Dim lastRow As Long
' Point to the worksheet and structured table.
Set ws = ThisWorkbook.Worksheets("Sales")
Set tbl = ws.ListObjects("SalesTable")
' A header-only table has no data body.
If tbl.DataBodyRange Is Nothing Then
MsgBox "The table has no data rows."
Exit Sub
End If
' Return the worksheet row number of the final data record.
lastRow = tbl.DataBodyRange.Row + _
tbl.DataBodyRange.Rows.Count - 1
MsgBox lastRow
End Sub
The calculation adds the body’s starting worksheet row to its height and subtracts one, producing the final row number. It works whether the table starts near the top of the sheet or farther down. The Long data type is suitable for worksheet row numbers, which can exceed the range of an Integer.
This example displays the result in a message box. In a larger macro, you might instead use lastRow to copy the final record, write a value beneath the table, or define a range. To refer to a particular table column, use the table’s column collection rather than assuming that a worksheet column letter will always remain correct.
Getting the table’s final row, including a totals row
DataBodyRange deliberately excludes both the header and the totals row. That is generally what you want when “last row” means the last actual record. If the table has a totals row enabled and you want the final row of the entire table range instead, use tbl.Range:
Sub ShowLastRowOfEntireTable()
Dim ws As Worksheet
Dim tbl As ListObject
Dim lastTableRow As Long
' Point to the worksheet and structured table.
Set ws = ThisWorkbook.Worksheets("Sales")
Set tbl = ws.ListObjects("SalesTable")
' This includes the header and, if present, the totals row.
lastTableRow = tbl.Range.Row + tbl.Range.Rows.Count - 1
MsgBox lastTableRow
End Sub
For a table with a totals row, that result is the totals row’s worksheet row. Without a totals row, it is the last data row when the table has records, or the header row for a header-only table. Pick the range based on what the next operation should include; do not treat the table’s overall range and its data body as interchangeable.
When a known column is the better reference
If the data is not an Excel table, or you specifically need the last populated cell in a reliable key column, the common VBA pattern is End(xlUp). It starts at the bottom of the worksheet and moves upward to the next non-empty cell, much like pressing Ctrl+Up from the bottom of a column.
Sub FindLastRowInColumnA()
Dim ws As Worksheet
Dim lastRow As Long
' Use the worksheet that contains the data.
Set ws = ThisWorkbook.Worksheets("Sales")
' Find the last non-empty cell in column A.
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
MsgBox lastRow
End Sub
This is concise and useful when column A is guaranteed to contain a value for every record. It is not automatically equivalent to finding a table’s last row. If the final record has a blank in column A, the result will be an earlier row. If column A is entirely empty, the expression returns row 1, which may look like a valid result even though no data was found. Choose a column that is required for every record, or use a table object when one exists.
Blank cells in the middle of a column do not prevent End(xlUp) from finding a later non-empty cell. However, formulas that return an empty string are still formulas in cells, so they can affect what a search based on cell contents or formulas considers occupied. Think about whether “last” means a cell containing a formula, a displayed value, or a real record.
Finding the last used row across a worksheet
Sometimes the data can be in any column and the goal is the last row containing a formula or value anywhere on the sheet. In that case, Range.Find can search by rows in reverse order. Specify the search options in the code; Excel can retain Find settings from earlier searches, so relying on omitted options may lead to surprising results.
Sub FindLastUsedRowOnSheet()
Dim ws As Worksheet
Dim lastCell As Range
Dim lastRow As Long
' Search the intended worksheet.
Set ws = ThisWorkbook.Worksheets("Sales")
' Search formulas and constants, moving backward by rows.
Set lastCell = ws.Cells.Find(What:="*", _
After:=ws.Cells(1, 1), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False)
' Find returns Nothing when no matching cell exists.
If lastCell Is Nothing Then
lastRow = 0
Else
lastRow = lastCell.Row
End If
MsgBox lastRow
End Sub
Here, zero is a deliberate signal that the sheet has no matching cell; worksheet row numbers start at 1, so it cannot be confused with a real row. The search uses xlFormulas, so it looks at formulas as well as constants rather than only what a formula displays. This method finds the last matching row on the sheet, not specifically the bottom of a named table.
Choosing the right method
Use the method that matches the structure and meaning of your data. A useful decision guide is:
- Named Excel table, last data record: use
ListObject.DataBodyRangeand handle an empty table. - Named Excel table, including its totals row: use the final row of
ListObject.Range. - Ordinary range with a dependable key column: use
Cells(Rows.Count, column).End(xlUp).Row. - Any content anywhere on a sheet: use
Findwith explicit search settings and check forNothing.
For code that will be maintained or used with changing data, a named table is often easier to reason about than a hard-coded column. It also gives the code a clear boundary: unrelated notes elsewhere on the worksheet do not become part of the table merely because they occupy a lower row.
Common errors and how to avoid them
A frequent error is attempting to read DataBodyRange.Rows.Count without checking for an empty table. When the table has no data rows, DataBodyRange is Nothing, so trying to access its properties causes a run-time error. Test the object first, as in the recommended example. A table with one or more records has a body range, even if some cells in those records are blank.
Another source of confusion is using UsedRange.Rows.Count as though it were the last worksheet row number. It is a count of rows in the used range, and that range may start below row 1. Formatting or past edits can also make the used range extend farther than the actual records. If you use it, account for its starting row; for an accurate content search, use an appropriate search or a known data column instead.
Finally, distinguish row numbers from counts. tbl.ListRows.Count answers “How many data rows are in this table?” It does not answer “What worksheet row is the final record on?” A table starting at row 4 with six records has a count of 6 and a last data row of 10. Mixing these values can cause code to read or write the wrong cells.
Running and adapting the VBA code
In Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor, then use Insert > Module and paste a procedure into the module. You can run a macro from the editor or from Excel’s Macro dialog. If the Developer tab is hidden, enable it through File > Options > Customize Ribbon. On a Mac, the VBA editor is available through Tools > Macro > Visual Basic Editor; the keyboard shortcut can depend on the keyboard’s function-key settings. Excel for the web can open a workbook containing macros, but it does not run VBA macros.
Change both the worksheet name and table name to match the workbook exactly. You can inspect table names by selecting a table and viewing the Table Design tab. If a macro will change cells, save a copy of the workbook before running it: VBA actions generally cannot be undone with Ctrl+Z. The examples above only report row numbers, but that precaution matters when adapting them to edit or delete data.
Frequently Asked Questions
How do I get the last data row of a table in VBA?
Reference the table’s DataBodyRange, check that it is not Nothing, and calculate its final row as the starting row plus its row count minus one. This excludes the header and totals row.
How do I find the last row if the table is empty?
Check whether tbl.DataBodyRange Is Nothing before using its properties. Decide what your procedure should do when there are no records—for example, exit, report zero, or handle the header row separately.
Does ListRows.Count return the worksheet row number?
No. It returns the number of data records in the table. For the worksheet row number, calculate the last row from DataBodyRange.Row and DataBodyRange.Rows.Count.
How can I include the table’s totals row?
Use the bottom row of tbl.Range instead of tbl.DataBodyRange. The overall range includes the header and includes the totals row when one is enabled.
Why does End(xlUp) give the wrong result?
It only searches the column you specify. If the last record is blank in that column, it returns an earlier populated row; if the column has no entries, it returns row 1. Use a dependable key column or reference the table directly.
Related
- Excel Shortcut for Inserting Rows Above or Below
- Excel Shortcut to Go to a Specific Column in Seconds
- Excel Shortcut to Insert a Cut Row
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Function Statement: Syntax, Scope, and ExamplesThe VBA Function statement declares a procedure that performs a task and returns a value. You can call…
- VBA Code to Add a Worksheet in Excel: Safe ExamplesTo add a worksheet with VBA in Excel, use Worksheets.Add. For example, this macro adds a sheet after…
- Excel VBA Copy Until Last Row: Reliable Code ExamplesTo copy a range in Excel VBA through the last populated row, find that row with End(xlUp), build…
- Excel VBA: Open a Workbook in the Background, HiddenYes. In Excel VBA, open the file with Workbooks.Open, then set each window in the returned workbook’s Windows…
- VBA Sort Data in Excel: How to Build a Reliable MacroYes. In Excel for Microsoft 365 on Windows, VBA can sort a range or table by one or…
- Excel VBA Expected Expression Error: Causes and FixesIn Excel VBA, Expected: expression is a compile error: VBA reached a place where it needs a value…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.