Delete Table Row in VBA: Excel Examples and Tips

To delete a row from an Excel table with VBA, use the table’s ListRows collection: identify the table with ListObjects, then call ListRows(index).Delete. For example, lo.ListRows(3).Delete removes the third data row—not worksheet row 3—and leaves the table’s header and totals row in place.

What a table row means in VBA

An Excel table, also called a ListObject in VBA, is more than a formatted range. It has a defined name, header row, data body, optional totals row, and built-in behavior such as filters and structured references. For a table named SalesTable on a worksheet named Orders, VBA can refer to it as Worksheets("Orders").ListObjects("SalesTable").

The distinction between a table row and a worksheet row matters. A table’s first data row is ListRows(1), whether it sits on worksheet row 2, row 10, or row 100. The header is not part of the ListRows collection, and neither is the totals row. Deleting a table row therefore targets the record in the table rather than blindly removing a numbered row from the sheet.

To find a table’s name, click a cell inside it and look for the table name on the Table Design tab in Excel for Microsoft 365 on Windows. The worksheet name and table name are separate: the former identifies the sheet, while the latter identifies the table on that sheet.

Delete one row by its position

Use this pattern when you know the row’s position among the table’s data records. Change the worksheet, table, and index to match your workbook. The example checks the count first so it does nothing if the requested row does not exist.

Option Explicit

Sub DeleteThirdSalesRecord()
    Dim ws As Worksheet
    Dim lo As ListObject
    Dim rowIndex As Long

    ' Select the worksheet and table by name.
    Set ws = ThisWorkbook.Worksheets("Orders")
    Set lo = ws.ListObjects("SalesTable")
    rowIndex = 3

    ' ListRows counts data records only, not headers or totals.
    If rowIndex >= 1 And rowIndex <= lo.ListRows.Count Then
        lo.ListRows(rowIndex).Delete
    End If
End Sub

The index is relative to the table’s data body. If the table begins on worksheet row 8, ListRows(3) still means its third data record, not worksheet row 3 or row 10 by a fixed formula. After deletion, the rows below it in the table move up and remain part of the table. Table formulas and structured references generally adjust as Excel updates the table.

Delete the row containing the active cell

A useful variation deletes the table record that contains the currently selected cell. This is safer than using ActiveCell.EntireRow.Delete, which removes a whole worksheet row and can affect unrelated content, formulas, or other tables alongside the target table.

Option Explicit

Sub DeleteActiveTableRecord()
    Dim ws As Worksheet
    Dim lo As ListObject
    Dim hit As Range
    Dim rowIndex As Long

    ' Set these names to the worksheet and table you intend to edit.
    Set ws = ThisWorkbook.Worksheets("Orders")
    Set lo = ws.ListObjects("SalesTable")

    ' There is no data body when the table has no records.
    If lo.DataBodyRange Is Nothing Then Exit Sub
    If Not (ActiveSheet Is ws) Then Exit Sub

    ' Continue only when the active cell is inside a data row.
    Set hit = Intersect(ActiveCell, lo.DataBodyRange)
    If hit Is Nothing Then Exit Sub

    rowIndex = hit.Row - lo.DataBodyRange.Row + 1
    lo.ListRows(rowIndex).Delete
End Sub

The range check is important. Clicking a table header or totals cell does not make it a data row, so the macro exits without deleting anything. If you want the macro to act on a different table, change the names explicitly rather than relying on whichever table happens to be selected.

Delete rows that match a value

For cleanup tasks, it is often better to identify records by their contents than by position. The following example removes every record whose Status value is Closed. It searches the Status table column by name, compares without regard to capitalization, and works upward from the last row. Deleting from bottom to top avoids skipping a record when the remaining rows shift after a deletion.

Option Explicit

Sub DeleteClosedSalesRecords()
    Dim ws As Worksheet
    Dim lo As ListObject
    Dim statusCol As Long
    Dim i As Long
    Dim statusValue As Variant

    ' Set the worksheet and table names for your workbook.
    Set ws = ThisWorkbook.Worksheets("Orders")
    Set lo = ws.ListObjects("SalesTable")

    ' An empty table has no data rows to examine.
    If lo.DataBodyRange Is Nothing Then Exit Sub
    statusCol = lo.ListColumns("Status").Index

    ' Work upward so a deletion does not skip the next record.
    For i = lo.ListRows.Count To 1 Step -1
        statusValue = lo.DataBodyRange.Cells(i, statusCol).Value
        If Not IsError(statusValue) Then
            If StrComp(CStr(statusValue), "Closed", vbTextCompare) = 0 Then
                lo.ListRows(i).Delete
            End If
        End If
    Next i
End Sub

The column name must match a table header, including spaces and punctuation. If the table does not contain a Status column, VBA will raise an error when it looks up that column. Check the header spelling before running the macro. The code also leaves cells containing Excel errors alone rather than trying to convert an error value to text.

Handle empty tables, headers, and totals rows

A table can exist even when it has no data rows. In that situation, ListRows.Count is zero and DataBodyRange is Nothing. Checking DataBodyRange Is Nothing before accessing cells is a clear way to make a macro safe for an empty table. It also avoids assuming that a header-only table has a first data row.

Because ListRows represents data rows only, a totals row is not deleted by ListRows(1).Delete. That is usually the intended behavior when removing a record. If your goal is to remove or change the totals row itself, use the table’s totals-row properties rather than treating it as a data record. Likewise, do not use a data-row index to try to remove the header.

If a table is filtered, its records may include rows currently hidden by the filter. A loop through ListRows examines the table’s rows, not just the rows visible on screen. The criteria-based example above therefore removes every matching Closed record, including filtered-out matches. If you intend to affect only visible records, make that requirement explicit and use a method designed for visible cells; do not assume that a filter makes hidden records disappear from the collection.

Table row deletion versus worksheet row deletion

For a record that belongs to an Excel table, ListRows(i).Delete is normally the right tool. It removes the record from the table and lets Excel maintain the table structure. By contrast, Rows(i).Delete or EntireRow.Delete deletes a worksheet row. That can shift unrelated cells elsewhere on the sheet and may affect other content that is not part of the table.

Use a worksheet-row deletion only when the intention really is to remove the entire sheet row, including cells outside the table. Even then, be careful if the row contains formulas, notes, or data belonging to a separate report. A common misconception is that deleting worksheet row 12 is equivalent to deleting the twelfth table record. It is not: the table may start lower down, and its records may have been sorted or filtered.

You can also remove a table record manually: right-click a cell in the data row and choose Delete > Table Rows. That is convenient for occasional edits. VBA is more useful when the operation must be repeated, driven by a condition, or applied consistently across many records.

Common errors and practical safeguards

Wrong table name: A misspelled name, or using a sheet name where a table name is needed, causes an object lookup error. Confirm both names in Excel before running the procedure.
Invalid index: An index of zero or a number greater than ListRows.Count is not a valid data-row position. Check the range before deleting, especially when the table changes size.
Deleting while counting upward: If a loop deletes row 2, the former row 3 moves into position 2. An upward loop can then advance past it. Iterate from the last row to the first when removing multiple matches.
Assuming the active cell is in the table: Validate the active sheet and intersect the selection with DataBodyRange before translating it into a row index.
Removing the wrong scope: EntireRow.Delete affects the worksheet row, not just the table record. Use the ListRows collection when the target is a table record.

Before running a deletion macro, save a separate copy of the workbook or test the code on sample data. VBA deletions cannot be reversed with Ctrl+Z, even if the macro removes the wrong records. A workbook containing macros should be saved in a macro-enabled format such as .xlsm if you need to retain the VBA project.

Running VBA in Windows, Mac, and Excel for the web

These examples use Excel for Microsoft 365 on Windows, but the core ListObject methods are also available in Excel for Microsoft 365 on Mac. On Windows, press Alt+F11 to open the Visual Basic Editor, insert a standard module through Insert > Module, and paste in the procedure. Run it from the editor or from Excel’s macro controls after saving the workbook appropriately.

On a Mac, the Visual Basic Editor can be opened with Option+F11; some keyboards require Fn+Option+F11. The exact shortcut can depend on the keyboard and macOS settings, so the Developer tab’s Visual Basic command is an alternative when available. The VBA editor and macro support are desktop features. Excel for the web can open and edit many workbooks, but it cannot run VBA macros; use the desktop app to execute this code.

Frequently Asked Questions

How do I delete a specific row from an Excel table using VBA?
Reference the table with Worksheets("SheetName").ListObjects("TableName"), then call ListRows(index).Delete. The index starts at 1 for the first data row and excludes the header and totals row.

How do I delete a table row based on a cell value?
Find the column index with ListColumns("HeaderName").Index, inspect each row’s value, and delete matching rows. When removing multiple records in a loop, iterate from the last row back to the first so shifting rows do not cause skipped checks.

Does ListRows(1) include the header?
No. ListRows(1) is the first data record. The table header is separate, and an optional totals row is also outside the ListRows collection.

Can I undo a VBA row deletion with Ctrl+Z?
No. Running a VBA macro clears Excel’s normal undo history, and Ctrl+Z will not restore the deleted table record. Save a backup or test on a copy before running deletion code.

Can VBA delete only visible rows in a filtered table?
Not automatically by using ListRows; that collection includes records hidden by filtering. The criteria-based example checks every row. If only visible records should be removed, build and verify a visible-cells-specific approach before deleting.

Can Excel for the web run this VBA macro?
No. VBA macros run in the Excel desktop application, including supported Windows and Mac versions. Open the workbook in desktop Excel to run the procedure.

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.