Excel VBA: Delete a Row from a Table

To delete a data row from an Excel table with VBA, use the table’s ListRows collection: for example, tbl.ListRows(3).Delete removes the third data row. Set tbl to the relevant ListObject first; its row index counts table data rows, not worksheet row numbers.

How Excel table row deletion works

An Excel table, also called a ListObject in VBA, has a structured range with table-specific features such as filter buttons, column names, and calculated columns. A table’s ListRows collection contains its data rows. Calling Delete on one of those rows removes that record from the table and updates the table’s data range.

This is different from deleting a worksheet row with Rows(5).Delete or EntireRow.Delete. Those commands target worksheet rows, which can affect cells beyond the table. Use ListRows(index).Delete when your intention is to remove a record from a table. If other data sits beside or below the table, inspect the layout and test on a copy: worksheet-level deletion and table-row deletion are not interchangeable operations.

Table row indexes begin at 1 for the first data row, regardless of the row number where the table appears. The header and optional totals row are not members of ListRows. So ListRows(1) means the first record beneath the header, not worksheet row 1.

Delete a specific row by its table position

For a known table name and a known data-row position, this basic macro checks that the row exists before deleting it. Change the worksheet, table, and index to match your workbook.

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

    ' Identify the worksheet and table.
    Set ws = ThisWorkbook.Worksheets("Sales")
    Set tbl = ws.ListObjects("Table1")

    ' ListRows counts data rows only; do nothing if row 3 is absent.
    If tbl.ListRows.Count >= 3 Then
        tbl.ListRows(3).Delete
    End If
End Sub

Run the macro from the Visual Basic Editor, or call it from another procedure. On Windows, open the editor with Alt+F11 or use Developer > Visual Basic. On a Mac, Option+F11 is commonly the editor shortcut, though some keyboards require Fn as well; the Developer tab is another route. Excel for the web can open a workbook containing VBA, but it cannot run or edit VBA macros.

Use the table’s actual name, which you can find by selecting a table cell and checking the Table Design tab’s Table Name box. Worksheet names and table names are separate: a worksheet called “Sales” may contain a table named “Table1.”

Delete the row containing the active cell

If a user selects any cell in a record and then runs a macro, calculate the selected cell’s position inside the table’s data body. The following example refuses to delete anything if the active cell is on another sheet, outside the table, or in an empty table.

Sub DeleteRowWithActiveCell()
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim hit As Range
    Dim rowIndex As Long

    Set ws = ThisWorkbook.Worksheets("Sales")
    Set tbl = ws.ListObjects("Table1")

    ' An empty table has no DataBodyRange to intersect.
    If tbl.ListRows.Count = 0 Then Exit Sub
    If Not (ActiveCell.Worksheet Is ws) Then Exit Sub

    Set hit = Intersect(ActiveCell, tbl.DataBodyRange)
    If hit Is Nothing Then
        MsgBox "Select a cell in a table data row first."
        Exit Sub
    End If

    ' Convert the worksheet row to a ListRows index.
    rowIndex = hit.Row - tbl.DataBodyRange.Row + 1
    tbl.ListRows(rowIndex).Delete
End Sub

The macro removes the entire table record, not just the selected cell or column value. That is usually what “delete a row from a table” means. If you want to keep the record but clear one field, assign an empty value to the relevant cell instead of deleting the ListRow.

Delete rows that match a condition

For cleanup tasks, it is common to remove every record whose value in a named column meets a condition. Delete from the bottom of the table upward. After a deletion, rows below the deleted record move up in the table; a forward loop can therefore skip the next record as the indexes change.

Sub DeleteCancelledRows()
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim i As Long
    Dim statusValue As Variant

    Set ws = ThisWorkbook.Worksheets("Sales")
    Set tbl = ws.ListObjects("Table1")

    ' Avoid accessing DataBodyRange in an empty table.
    If tbl.ListRows.Count = 0 Then Exit Sub

    ' Work upward so remaining row indexes do not shift past the loop.
    For i = tbl.ListRows.Count To 1 Step -1
        statusValue = tbl.ListColumns("Status").DataBodyRange.Cells(i, 1).Value
        If Not IsError(statusValue) Then
            If StrComp(CStr(statusValue), "Cancelled", vbTextCompare) = 0 Then
                tbl.ListRows(i).Delete
            End If
        End If
    Next i
End Sub

This version compares the Status value with “Cancelled” without distinguishing uppercase and lowercase letters. It skips cells containing Excel error values. The column name must match the table header; if the header is renamed, update the VBA reference. To delete only the first match, add an Exit For after the delete statement. For large tables, deleting many rows one by one may take time, so test performance with a representative copy of the data.

Find the correct table and column names

VBA needs the exact table name, and criteria-based code also needs the exact column header. A table’s name appears in Table Design when the table is selected. You can also list table names in the Immediate window by inspecting ws.ListObjects in the editor. Spaces in a column heading are allowed; refer to the complete heading as a string, such as tbl.ListColumns("Order Status").

When a workbook has several tables, avoid relying on whichever table happens to be selected. Explicitly qualify the worksheet and table as in the examples. ThisWorkbook means the workbook that contains the running VBA code; ActiveWorkbook means the workbook currently active in Excel. The distinction matters if a macro opens or switches between workbooks.

Common errors and edge cases

An “index out of range” error often means the macro requested a row number greater than ListRows.Count, or the table name is wrong. Check the count before deleting a known index. A “subscript out of range” error when setting the table can also result from a misspelled worksheet or table name.

Empty table: It has no data rows, and DataBodyRange is Nothing. Check ListRows.Count before using the data body.
Header or totals row: These are not data rows. A ListRows index cannot be used to target the header or totals row.
Filtered table: A filter hides records; it does not remove them. A loop through all ListRows can still examine hidden rows. Decide whether the macro should process all records or only visible ones.
Formulas and references: Removing a record changes the table’s rows. Table formulas and structured references generally adjust with the table, but check dependent formulas, charts, and downstream processes after running a deletion.
External or refreshed data: If a table is loaded from a query or other source, a refresh may bring deleted records back or replace the table’s contents. Where possible, remove the record in the source or adjust the query rather than treating the loaded table as the permanent data store.

Another frequent mistake is assuming a table row index equals its worksheet row number. If the header is on worksheet row 8, the first data row is typically on row 9, but its VBA table index is still 1. Use the table’s ListRows index or calculate a position from DataBodyRange; do not pass a worksheet row number directly to ListRows.

Choose between deleting, clearing, and filtering

Delete a ListRow when the record should no longer belong to the table. Clear cell contents when the row must remain as a placeholder or preserve a fixed layout. Filter the table when the goal is only to hide records from view; filtering is reversible and does not erase data. If records need an audit trail, consider adding an “Archived” or “Inactive” status and filtering those rows out instead of permanently deleting them.

For a one-off manual removal, select a cell in the table row, right-click, and choose Delete > Table Rows. That menu action avoids writing code. VBA becomes useful when the same rule must be applied repeatedly, when a button should run the cleanup, or when multiple records must be processed consistently. A macro should express the intended scope clearly: table record, worksheet row, or cell contents.

Run macros safely

Save a copy of the workbook before running a deletion macro. VBA actions cannot be undone with Ctrl+Z, and an accidental deletion may not be recoverable from the current session. Save a workbook containing VBA in a macro-enabled format such as .xlsm; saving it as a standard .xlsx file does not retain VBA code.

Only enable macros from workbooks you trust. Before using a macro on important data, test it on a copy with a few representative records, confirm the table and column names, and check the results after it runs. For a recurring process, consider adding a confirmation prompt, a log of deleted records, or an archive step. Those safeguards are especially useful when the deletion rule is based on user-entered values that may contain spelling variations or unexpected errors.

Frequently Asked Questions

How do I delete a table row by its worksheet row number?
Convert the worksheet row to a table data-row index, or locate the row within DataBodyRange as in the active-cell example. Do not use the worksheet row number directly as a ListRows index.

Does ListRows(1).Delete delete the table header?
No. It deletes the first data row. The header and totals row are not included in the table’s ListRows collection.

Why does my loop skip some matching rows?
Deleting a row changes the indexes of the rows after it. Iterate from the last row to the first, as the condition-based example does, to avoid skipping records.

Can I delete a row from a filtered table?
Yes, but decide whether the macro should inspect all table records or only visible records. A normal loop over ListRows processes the collection, including records hidden by a filter.

Can I undo a VBA row deletion with Ctrl+Z?
No. Excel clears its normal undo history when VBA changes the workbook. Save a backup or copy before running a deletion macro.

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.