[Excel VBA] How to Batch Delete Rows Hidden by AutoFilter

When organizing data, a common scenario involves extracting only necessary data using Excel’s AutoFilter function and then deleting all other rows that were hidden by the filter.

Performing this manually is difficult, but VBA can automate this process.

This article explains a highly effective technique using SpecialCells(xlCellTypeVisible) to strictly and rapidly batch delete only the rows hidden by a filter.

Completed VBA Code

The following code filters Column A for “Tokyo” and deletes all rows hidden by the filter (rows other than “Tokyo”).

Sub DeleteFilteredOutRows()

    ' Declare variables
    Dim dataArea As Range
    Dim visibleRows As Range
    
    ' Check if AutoFilter is applied to the active sheet
    If Not ActiveSheet.AutoFilterMode Then
        MsgBox "AutoFilter is not set on this sheet.", vbExclamation
        Exit Sub
    End If
    Set dataArea = ActiveSheet.AutoFilter.Range

    ' --- 1. Filter for the data to KEEP ---
    ' (In this example, extract data where Column A is "Tokyo")
    dataArea.AutoFilter Field:=1, Criteria1:="Tokyo"

    ' --- 2. Store the extracted visible cells (rows to keep) in a variable ---
    ' Use SpecialCells(xlCellTypeVisible) to get only visible cells
    On Error Resume Next ' Avoid error if filter result is 0
    Set visibleRows = dataArea.SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
    
    If visibleRows Is Nothing Then
        MsgBox "No data extracted. Process aborted."
        Exit Sub
    End If

    ' --- 3. Temporarily release the filter ---
    ' This removes the filter dropdowns. 
    ' Even if rows unhide here, the Range object 'visibleRows' preserves the reference.
    ActiveSheet.AutoFilterMode = False

    ' --- 4. [Important] Invert visibility: Hide the rows to KEEP ---
    ' By hiding the "Keepers", only the "Rows to Delete" remain visible.
    visibleRows.EntireRow.Hidden = True

    ' --- 5. Batch delete the currently visible rows (Rows to Delete) ---
    dataArea.SpecialCells(xlCellTypeVisible).EntireRow.Delete
    
    ' --- 6. Unhide the rows that were kept ---
    ActiveSheet.Rows.Hidden = False

    MsgBox "Deleted rows that were hidden by the filter."

End Sub

Explanation of Code Points (Process Flow)

This code uses a specific logical inversion to ensure accuracy and speed.

1. Extract Data and Identify Visible Cells

dataArea.AutoFilter Field:=1, Criteria1:="Tokyo"
Set visibleRows = dataArea.SpecialCells(xlCellTypeVisible)

First, execute the AutoFilter normally to display the data to be kept. Use SpecialCells(xlCellTypeVisible) to hold a reference to that visible cell range (visibleRows).

2. Release Filter and Invert Visibility

ActiveSheet.AutoFilterMode = False
visibleRows.EntireRow.Hidden = True

This is the core of the technique.

1. Release Filter Mode: Remove the filter.
2. Hide “Keepers”: Force the rows stored in the variable (visibleRows) to be hidden using .Hidden = True.

As a result, a “reversal” occurs on the screen: Only the “Rows to Delete” are displayed, and all “Rows to Keep” are hidden.

3. Delete Visible Rows and Restore

dataArea.SpecialCells(xlCellTypeVisible).EntireRow.Delete
ActiveSheet.Rows.Hidden = False

Once the state is inverted:

1. Use SpecialCells(xlCellTypeVisible) again to identify the cells currently visible on the screen (the rows to delete).
2. Execute .EntireRow.Delete to delete them all at once.
3. Finally, return the sheet’s hidden setting (.Rows.Hidden) to False to reveal the “Rows to Keep” that were hidden in step 2.

Summary

The method for deleting data not extracted by a filter involves the following logic:

1. Identify rows to keep using SpecialCells(xlCellTypeVisible).
2. Release the filter and hide the “Rows to Keep”.
3. Identify the “Rows to Delete” (which are now visible) using SpecialCells again and delete them.
4. Unhide the “Rows to Keep”.

Compared to looping through rows one by one, this method using SpecialCells offers the significant advantage of extremely fast processing even with large amounts of data.