Excel VBA: Delete Visible Rows After AutoFilter

To delete only the rows currently visible in a standard Excel AutoFilter, use VBA to take the filter range without its header, select its visible cells, and delete their entire rows. Apply the filter first and save a copy of the workbook: deleting rows with a macro cannot be reversed with Undo.

What the macro deletes

An AutoFilter hides records that do not match the selected criteria; it does not remove them. For example, if a list of orders is filtered to show only the region West, the other regions remain in the worksheet but are hidden. A visible-row deletion macro removes the records that remain on screen and leaves the filtered-out records in place.

The key distinction is between the header and the data. A filter range includes its header row, so the macro below starts one row below that header. It uses SpecialCells(xlCellTypeVisible) to identify visible cells, then deletes their worksheet rows. The macro works on the active sheet’s regular AutoFilter range, so make sure the correct sheet and filter are active before running it.

VBA macro for a regular AutoFilter

Use this version for a filtered range with dropdown arrows created through Excel’s Data tab or Home tab. It checks for a filter, excludes the header, and handles the case where filtering leaves no visible records.

Sub DeleteVisibleFilteredRows()
    Dim ws As Worksheet
    Dim filterRange As Range
    Dim dataRange As Range
    Dim visibleData As Range

    Set ws = ActiveSheet

    ' Require a worksheet AutoFilter before continuing.
    If Not ws.AutoFilterMode Then
        MsgBox "Apply an AutoFilter first.", vbExclamation
        Exit Sub
    End If

    Set filterRange = ws.AutoFilter.Range

    ' A one-row range contains a header but no records.
    If filterRange.Rows.Count <= 1 Then
        MsgBox "The filter range has no data rows.", vbInformation
        Exit Sub
    End If

    ' Exclude the header row and keep all filter columns.
    Set dataRange = filterRange.Offset(1, 0).Resize( _
        filterRange.Rows.Count - 1, filterRange.Columns.Count)

    ' SpecialCells raises an error if no data cells are visible.
    On Error Resume Next
    Set visibleData = dataRange.SpecialCells(xlCellTypeVisible)
    On Error GoTo 0

    If visibleData Is Nothing Then
        MsgBox "No data rows are visible.", vbInformation
        Exit Sub
    End If

    ' Remove the complete worksheet rows for the visible records.
    visibleData.EntireRow.Delete
End Sub

Place the code in a standard VBA module, not in a worksheet cell. In Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, paste the code, and run DeleteVisibleFilteredRows while the target worksheet is active. On Mac, Option+F11 opens the editor on many keyboards; some require Fn+Option+F11. Excel for the web does not run VBA macros, so use the desktop app.

Run it safely, step by step

Save a separate copy of the workbook before running the macro. VBA row deletion is not reversible with Ctrl+Z on Windows or Command+Z on Mac.
Confirm the list has a single, clearly identified header row and that the AutoFilter covers the full list, including every column belonging to each record.
Set the filter criteria so the rows you intend to remove are the rows visible on the sheet. Check the visible records rather than relying only on the filter dropdown’s selection.
Activate the worksheet containing that filter, then run the macro from the VBA editor or from the Developer tab’s Macros command.
Review the remaining records and save the workbook under an appropriate name. Clear the filter afterward if you need to inspect the rows that were retained.

Be especially careful when no filter criteria are restricting the list. In that state, every data row in the AutoFilter range is visible, so the macro will delete every record in that range. A filter that returns no records has the opposite outcome: there are no visible data rows to delete, and the macro displays a message instead.

Why the header and filter range matter

Deleting the header along with the records can remove the field names and disrupt the list’s filter controls. That is why the code uses Offset(1, 0) and reduces the row count by one. It preserves the filter range’s width so that a row is identified as a record based on the full set of columns, not just the field used for filtering.

The code relies on ws.AutoFilter.Range, which refers to the active worksheet’s AutoFilter range. It does not guess the list from the current selection or from the worksheet’s last used row. This is useful when the filter covers a well-defined block, but it also means that an incorrectly configured filter range can produce an incorrect deletion. If the list has notes or unrelated data immediately beside it, check that the filter range includes only the intended records and their headers.

The macro deletes entire worksheet rows, not just the cells inside the filtered columns. That is normally right when each worksheet row is one record and the other columns on that row belong to the same record. If the sheet contains unrelated information in other columns on those same rows, deleting entire rows removes that information too. In that situation, reorganize the data or use a method designed to remove only selected cells rather than whole records.

Standard ranges versus Excel tables

An Excel table has built-in filter arrows and may look like a regular AutoFilter range, but it is a ListObject with its own row structure. The main macro is written for a regular worksheet AutoFilter. Do not assume it will correctly identify a table’s filter range in every workbook setup; test on a copy if the filtered data is a table.

For tables, the safer conceptual approach is to inspect the table’s data rows and delete matching visible table rows through the table’s row collection. A table-specific macro should identify the table by name and target its DataBodyRange, rather than infer the target from whichever worksheet happens to be active. This matters when a sheet contains several tables or more than one filtered list. After deletion, Excel adjusts the table’s size and retains its header and table formatting.

If the table is small, another practical option is to filter it, select the visible table body rows, and use the table row deletion command from the context menu. In either case, verify that the selection excludes the header and totals row. A table’s Total Row is not a data record, and it should not be removed as though it were one.

Common problems and how to avoid them

The macro says no filter is applied: The code checks AutoFilterMode on the active worksheet. Activate the sheet with the regular AutoFilter, and confirm its dropdown arrows are visible. A filter on another sheet does not qualify.
It deletes more rows than expected: Check whether the filter is actually restricting the list. With all records visible, all records in the filter range are eligible for deletion. Also confirm the filter range does not include unrelated rows.
It leaves rows you thought were visible: Manually hidden rows are not visible cells, even if they are not excluded by the filter criteria. The macro uses Excel’s visible-cell status, so manually hidden rows are not selected.
It removes information outside the list: EntireRow.Delete removes the entire worksheet row. Keep unrelated information on a separate sheet or use a carefully designed approach that targets only the intended data structure.
The filter results are empty: There is nothing visible in the data body to delete. The macro handles this by showing a message instead of trying to delete an empty range.
It cannot be undone: Ctrl+Z on Windows and Command+Z on Mac do not restore rows deleted by VBA. Close without saving only if you are certain you do not need any other changes made since the last save; a saved backup is safer.

When to delete, hide, or move records

Deleting is appropriate when the matching records should be permanently removed from the working dataset. If you might need them later, consider copying the filtered rows to an archive sheet or workbook before deleting them. Another option is to add a status field such as Archived and filter that field out of everyday views. Both approaches preserve a record of the data while keeping the active list manageable.

Hiding rows is not the same as deleting them. Filters hide nonmatching records and can be cleared to show them again; deletion removes the records from the worksheet. Similarly, clearing a filter does not undo a deletion. If the purpose is only to print or review a subset, leave the rows filtered rather than running a destructive macro.

For a one-time cleanup, the macro can save manual selection work. For a recurring process, consider making the target explicit—for example, asking the user to confirm the filter or selecting a known table by name. This reduces the risk of running a correct deletion routine on the wrong sheet or with the wrong criteria.

Frequently Asked Questions

Does this delete only filtered rows or every hidden row?
The macro selects visible cells in the AutoFilter data range and deletes their entire rows. Rows hidden by the filter are not selected. Manually hidden rows are also not visible and therefore are not selected, though the exact result depends on Excel’s visible-cell state.

Will it delete the header row?
No. The code starts with the row immediately below the filter range’s first row, treating that first row as the header. Confirm the filter range has the correct header; a range with no real header can make the macro skip the first record.

Can I undo the macro with Ctrl+Z?
No. Excel clears its normal Undo history when VBA changes the worksheet. On Mac, Command+Z has the same limitation. Save a separate copy before running a deletion macro.

Can I run this in Excel for the web?
No. Excel for the web can display and edit many workbooks containing macros, but it does not execute VBA. Open the workbook in desktop Excel to run this code.

Does the macro work with a filtered Excel table?
This example is intended for a regular worksheet AutoFilter. Tables use their own ListObject and row collection, so use a table-aware routine or delete selected table rows through the table interface after checking the selection.

What if I want to delete the rows that are filtered out?
This macro does the reverse: it deletes rows that remain visible. To remove excluded records, the logic must target hidden rows, which is more delicate because manually hidden rows can be mixed with filter-hidden rows. Test a separate version on a copy and verify the target rows before deletion.

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.