Delete the Last Row in Excel VBA Safely

To delete the last data row of an Excel table, use the table’s ListRows collection: tbl.ListRows(tbl.ListRows.Count).Delete. For a worksheet row, first identify the last row that contains data, then delete that entire row; the right method depends on whether you mean a table record or a row on the sheet.

First decide what “last row” means

Excel tables and worksheets have different row structures. A table, created with Insert > Table, has a defined data area and may also have a header row and a totals row. The table’s data rows are represented in VBA by its ListRows collection. Deleting the final item in that collection removes the last table record without asking you to guess a worksheet row number.

A worksheet row, by contrast, spans all columns. Deleting one shifts the rows below it upward and can affect cells outside a table, including notes or other data blocks. A command that deletes the last used worksheet row is therefore not always the same as deleting the table’s last record. Choose the table-specific method when the target is a record in a named Excel table.

Delete the last data row from an Excel table

In the example below, the worksheet is named Data and the table is named Sales. Change both names to match the workbook. The macro checks that the table contains a data row before trying to delete one:

Sub DeleteLastTableRow()
    Dim ws As Worksheet
    Dim tbl As ListObject

    ' Set these names to match the workbook.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set tbl = ws.ListObjects("Sales")

    ' Do nothing when the table has no data rows.
    If tbl.ListRows.Count = 0 Then Exit Sub

    ' Delete the final data row, not the header or totals row.
    tbl.ListRows(tbl.ListRows.Count).Delete
End Sub

ListRows.Count returns the number of data rows in the table. If there are five, the final one has index 5. Calling Delete on that list-row object removes the record from the table and adjusts the table’s data area. The header remains, and a totals row, if enabled, remains the table’s totals row rather than being treated as a data record.

The workbook reference matters. ThisWorkbook means the workbook that contains the macro. If a macro stored in a personal macro workbook is meant to act on the currently active workbook, you may instead need ActiveWorkbook, but that is less predictable: the active workbook can change while code runs. Use an explicit workbook reference where possible.

What table-row deletion changes

Deleting a table row removes its cell contents and the table row itself. Excel updates the table’s range and, in many cases, fills calculated-column formulas into remaining rows according to the table’s normal behavior. Structured references that refer to the table can continue to use its adjusted data. The macro does not delete the worksheet row as a whole, so unrelated cells beside the table are not intentionally shifted.

This difference is important when a sheet contains several side-by-side blocks. If you delete a worksheet row, every cell on that row is affected, not just the cells inside the table. A table-row deletion is usually the safer option for removing one record from a structured list. It is not a substitute for checking that the table name points to the intended table: a wrong name can produce an error, and a valid but unintended name can remove the wrong record.

Delete the last worksheet row using a key column

If the data is an ordinary range rather than a table, use a column that should be populated in every record, such as an ID column. This example assumes row 1 contains headers and column A is filled for each data record:

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

    ' Set the worksheet that contains the list.
    Set ws = ThisWorkbook.Worksheets("Data")

    ' Find the last nonempty cell in the key column.
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Keep the header in row 1; delete only a data row.
    If lastRow > 1 Then ws.Rows(lastRow).Delete
End Sub

End(xlUp) starts at the bottom of column A and moves upward to the first nonempty cell. The guard prevents the macro from deleting row 1 when the sheet has only a header or no entries below it. If the table starts on a different row, change the guard to match that layout; for example, if headers are in row 4, only delete when lastRow > 4.

This approach depends on the key column being reliable. If the final record has a blank in column A, the code may stop at an earlier row and delete the wrong record. Pick a column that is consistently populated, or use a table’s ListRows collection instead.

Find the last used row across the sheet

When no single column is guaranteed to be complete, search the worksheet for its last cell containing a value or formula. This version searches by row and checks that the result is below the header:

Sub DeleteLastUsedWorksheetRow()
    Dim ws As Worksheet
    Dim lastCell As Range

    ' Set the worksheet that contains the data.
    Set ws = ThisWorkbook.Worksheets("Data")

    ' Search all cells for the final value or formula by row.
    Set lastCell = ws.Cells.Find(What:="*", _
        After:=ws.Cells(1, 1), LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, MatchCase:=False, _
        SearchFormat:=False)

    ' Do nothing if the sheet is empty or the result is the header.
    If Not lastCell Is Nothing Then
        If lastCell.Row > 1 Then ws.Rows(lastCell.Row).Delete
    End If
End Sub

Setting the search arguments explicitly is useful because Excel’s Find dialog can retain settings from a previous search. With LookIn:=xlFormulas, a formula is considered present even if its displayed result is an empty string. This can be appropriate when formulas are part of the data, but it may mean that a visually blank formula row is treated as the last row. Also, this method finds the last occupied row anywhere on the worksheet, so it can include a note, a separate report, or a footer below the list. Use it only when that matches your definition of “last.”

How to choose the safest method

Use ListRows when the target is the final record in a named Excel table. It is the clearest choice for structured data and avoids deleting unrelated cells across the worksheet row.
Use a key column when the list is a plain range and one field is always filled for every record. It is simple and fast, but its accuracy depends on that assumption.
Use Find when the last occupied row may be determined by several columns. Review the sheet first so a footer or another data area is not mistaken for the list’s end.
Use a fixed row number only when the workbook layout is genuinely fixed and controlled. Hard-coded row numbers can become incorrect when records are added or removed.

For repeatable workbooks, consider putting data in a named table and using the table-specific macro. Tables make the intended data boundary explicit, while “last used row” is a judgment based on worksheet contents that can change as users add notes or formulas.

Common errors and safeguards

A “Subscript out of range” error often means the worksheet or table name in the code does not exactly match the name in Excel. Worksheet tab names and table names are different: a table’s name is shown when a cell in the table is selected, under Table Design > Table Name. Check capitalization and spaces, and confirm the table is on the worksheet specified in the code.

An empty table needs special handling. If it has no data rows, its header still exists but ListRows.Count is zero. The check in the table macro exits without deleting anything. Similarly, the worksheet examples include a header guard so a one-row header-only list is not removed. Adjust those guards if your headers are on another row or if the sheet has no header.

Another frequent mistake is deleting a whole worksheet row when the intent is to remove only a table record. The worksheet version uses ws.Rows(lastRow).Delete, which shifts the rest of that row’s sheet cells upward. The table version uses tbl.ListRows(...).Delete, which targets the table data row. Do not swap these statements without considering neighboring content.

Finally, deleting a row with VBA cannot be reversed with Ctrl+Z. Save a copy of the workbook before running a macro, especially when the macro selects a row dynamically. Test first on a duplicate workbook or with a small sample, and verify the worksheet and table names before applying it to important data.

Run the VBA macro in Excel

In Excel for Microsoft 365 on Windows, save the workbook as an Excel Macro-Enabled Workbook (.xlsm) if it needs to retain VBA code. Open the Visual Basic Editor with Alt+F11, choose Insert > Module, paste the procedure into the module, and edit the worksheet and table names. Run the macro from the editor or use Developer > Macros to select and run it. If the Developer tab is not visible, enable it through File > Options > Customize Ribbon.

On Excel for Mac, VBA macros are supported in the desktop application, but Excel for the web cannot create, edit, or run VBA macros. On Mac, open the Visual Basic Editor from Tools > Macro > Visual Basic Editor; the available shortcut can vary with the keyboard and system settings. Macro security prompts and trusted-file settings should be treated cautiously: run only macros whose source and purpose you understand. A macro stored in an .xlsx file will not be retained as VBA when the workbook is saved in that format.

Frequently Asked Questions

How do I delete the last row of an Excel table without deleting the header?
Use tbl.ListRows(tbl.ListRows.Count).Delete after checking that tbl.ListRows.Count is greater than zero. The collection contains data rows, not the table header.

Does deleting a table row also delete the worksheet row?
No. Deleting a ListRow removes that row from the table, rather than deliberately removing the entire worksheet row and shifting all cells in it. Use ws.Rows(number).Delete only when a whole worksheet row should be removed.

How do I delete the last row if column A has blanks?
Do not rely on column A’s End(xlUp) result if the final record can have a blank there. Use a column that is always populated, a table’s ListRows collection, or a carefully scoped search across the relevant data area.

Can I undo a VBA row deletion with Ctrl+Z?
No. Running a VBA macro clears Excel’s normal undo history, so the deletion cannot be undone with Ctrl+Z. Save a copy first and confirm the target before running the procedure.

Can Excel for the web run this macro?
No. Excel for the web can open macro-enabled workbooks but does not run VBA macros. Use the desktop Excel application, including Excel for Windows or Excel for Mac, to execute VBA.

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.