VBA AutoFilter Delete: Remove Visible Rows Safely in Excel

Yes. In Excel VBA, use SpecialCells(xlCellTypeVisible) on the filtered data rows, then delete those rows; leave the header outside the range. Check that a filter is active first, because deleting visible rows with no filter can remove every record.

What “AutoFilter delete” means

AutoFilter hides rows that do not match the selected criteria; it does not remove them. “Delete with AutoFilter” usually means permanently removing the records that remain visible after filtering. For example, you might filter a Status column to show “Closed” and delete those records while keeping the other statuses.

That is different from clearing a filter. Clearing it makes hidden records visible again without deleting anything. It is also different from deleting only the contents of visible cells: deleting entire rows removes the records, while clearing cells leaves empty rows behind. Before running a macro, decide which of those outcomes you actually want.

Delete the visible filtered rows with VBA

The example below assumes the worksheet already has an active filter, the header is in row 1, and the records occupy columns A through D and rows 2 through 100. Change the range to match your sheet. It deliberately stops if Excel does not report an active filter.

Save a copy of the workbook first. A VBA macro cannot be undone with Ctrl+Z.
On Windows, press Alt+F11 to open the Visual Basic Editor. On Mac, use Option+F11; depending on your keyboard settings, you may also need Fn.
Choose Insert > Module, paste the code below, and change the worksheet range if needed.
Return to Excel, apply the filter criteria, then run the macro from the Visual Basic Editor or from Developer > Macros.

Sub DeleteVisibleFilteredRows()
    Dim ws As Worksheet
    Dim bodyRange As Range
    Dim visibleCells As Range

    Set ws = ActiveSheet
    ' Exclude the header row; adjust this range to fit the data.
    Set bodyRange = ws.Range("A2:D100")

    If Not ws.FilterMode Then
        MsgBox "Apply a filter before running this macro."
        Exit Sub
    End If

    ' A filter can leave no visible records, which raises an error here.
    On Error Resume Next
    Set visibleCells = bodyRange.SpecialCells(xlCellTypeVisible)
    On Error GoTo 0

    If visibleCells Is Nothing Then
        MsgBox "There are no visible data rows to delete."
        Exit Sub
    End If

    ' Deletes entire worksheet rows, not just cells in columns A:D.
    visibleCells.EntireRow.Delete
End Sub

SpecialCells(xlCellTypeVisible) selects cells that are not hidden. Because the range starts at A2, the header is not included in the deletion. With an active filter, rows that do not match the criteria are hidden and are therefore left alone. If the filter shows no records, the error handler allows the macro to reach the message rather than stopping with a runtime error.

Understand the range and deletion scope

The range in the example is an intentional limit, not a way for VBA to discover the correct data automatically. If records extend to row 500, change the range to include them. If there are more or fewer columns, adjust the column letters too. A range that stops too early can leave matching records behind; one that extends into unrelated rows can affect rows you did not mean to target.

There is an especially important consequence of EntireRow.Delete: Excel removes the whole worksheet row, across every column—not just columns A through D. If columns F onward contain another list aligned on the same rows, that information will also be deleted. Use whole-row deletion only when the rows represent records that should be removed across the sheet. If the data is in an Excel table or shares rows with other content, consider the table-specific approach below.

For a concrete example, suppose row 1 contains headers and rows 2 to 100 contain customer records, with status in column C. Filter column C to show “Closed,” confirm the visible records are the ones intended for removal, and run the macro. The matching visible records are deleted; the nonmatching filtered-out records remain. The filter may need to be reapplied or cleared afterward because the data has changed.

Deleting rows from an Excel table

Excel tables have their own structured rows and filter controls. Deleting entire worksheet rows can still work, but it may also remove unrelated material in those rows. When the goal is specifically to remove table records, delete the visible table rows as table rows instead. The following version assumes the filtered table is named Table1; find or change the name under Table Design > Table Name.

Sub DeleteVisibleRowsFromTable()
    Dim ws As Worksheet
    Dim lo As ListObject
    Dim i As Long

    Set ws = ActiveSheet
    Set lo = ws.ListObjects("Table1")

    If Not ws.FilterMode Then
        MsgBox "Apply a filter before running this macro."
        Exit Sub
    End If

    ' Work upward so deleting a row does not change the next index.
    For i = lo.ListRows.Count To 1 Step -1
        If Not lo.ListRows(i).Range.EntireRow.Hidden Then
            lo.ListRows(i).Delete
        End If
    Next i
End Sub

This version checks each table row from the bottom upward. The upward order matters: deleting a row does not shift the index of a table row the macro has yet to inspect. It deletes table records rather than explicitly deleting whole worksheet rows. As with the first macro, apply and check the filter first, and save a copy before running it.

Filter results, hidden rows, and what gets deleted

AutoFilter hides records that fail its criteria. A visible-cells method selects rows currently visible in the chosen range, rather than interpreting which filter criterion caused a row to be hidden. That distinction matters if someone has also manually hidden rows: manually hidden rows are not visible either, so the first macro will leave them out of its selection.

For many workflows, deleting visible results is exactly what is needed: filter for a category, inspect the result, then remove those records. But if the actual requirement is “delete every row whose value meets a condition,” a filter is not the only option. A loop that checks a particular column can express that rule directly. That approach also needs careful handling of headers, changing row numbers, and blank or unexpected values.

Do not confuse “visible” with “matching” in an unfiltered range. If no filter is active, most or all data rows are visible. That is why the example checks FilterMode before selecting cells. Even with that guard, verify the criteria and the visible records before running the deletion.

Common problems and how to avoid them

The header disappears. The range passed to SpecialCells includes the header. Start the body range on the first data row, not the heading row.
All records are deleted. There may be no active filter, or the filter criteria may show every record. Confirm the visible result and keep the active-filter check in the macro.
The macro reports no visible cells. The filter may return no matching records. The sample handles this case; check the criteria rather than removing the error handling.
Unrelated data disappears. EntireRow.Delete removes complete worksheet rows. Use the table-row method or redesign the operation if other content shares those rows.
Some records remain. The range may not cover all rows, or the filter may not include the records you intended. Expand the range and inspect the filter before trying again.
The macro will not run. Excel for the web cannot run VBA macros. Open the workbook in the desktop Excel app. In desktop Excel, the Developer tab may need to be enabled through File > Options > Customize Ribbon on Windows; the Mac ribbon settings are under Excel > Settings.

Also check whether the worksheet is protected. Protection can block row deletion unless the sheet’s permissions allow it. If the file came from an email or download, Excel may also restrict macros until the workbook is trusted. Do not enable macros from an unknown source simply to run a deletion.

Manual deletion and safer alternatives

You do not need VBA for a one-time cleanup. Apply the filter, select the visible data rows without the header, and use the worksheet’s delete-row command. Check the selection carefully: selecting cells and pressing Delete clears their contents, whereas deleting rows removes the records and shifts the remaining rows. Excel’s interface can vary depending on the selection and whether the range is a table, so confirm the command before accepting it.

For a repeatable process, a macro is useful because it can apply the same deletion steps consistently. If the goal is only to exclude records from a report, however, deleting source data may be unnecessary. Keep the original list and use a filtered view, a separate output sheet, or a Power Query transformation instead. These approaches preserve the source records and can be easier to review or repeat.

Choose based on the job:

One-time removal: filter, inspect, and delete through Excel’s interface.
Repeated cleanup: use a documented macro with a clearly defined range and a backup process.
Table data: delete table rows so unrelated worksheet content is less likely to be affected.
Reporting or reversible filtering: leave the source data intact and create a separate filtered result.

Safety checks before running a deletion macro

Deleting rows is a destructive operation. Excel does not provide the normal Ctrl+Z undo history for changes made by VBA, so do not treat running the macro as an experiment. Save a separate copy, especially if the workbook contains important records or formulas. If practical, test the macro on a small duplicate dataset first.

Before running it on the working file, check the worksheet name, the exact range, the filter field and criteria, and whether whole worksheet rows are safe to remove. Afterward, review the remaining records and any formulas, totals, or charts that depend on them. On Windows, Alt+F11 opens the VBA editor; on Mac, Option+F11 may require Fn. Excel for the web does not execute VBA, even if the workbook contains macro code.

Frequently Asked Questions

Does AutoFilter delete rows by itself?
No. AutoFilter hides rows that do not meet the criteria. You must delete the visible results separately, using a worksheet command or VBA.

How do I delete filtered rows but keep the header?
Pass only the data-body range to the deletion code. If the header is in row 1, start the range on row 2, as in A2:D100.

Does SpecialCells(xlCellTypeVisible) select filtered-out rows?
No. It selects cells that are visible in the specified range. Filtered-out rows are hidden and are not selected.

Can I undo a VBA row deletion with Ctrl+Z?
No. VBA actions generally clear Excel’s undo history. Save a backup copy before running a macro that deletes data.

Can I run this VBA in Excel for the web?
No. Open the workbook in desktop Excel for Windows or Mac to run VBA. Excel for the web can display a macro-enabled workbook, but it cannot execute its VBA macros.

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.