Filter Remover: Show All the Data Your Inherited Workbook Is Hiding

TL;DR: You open an inherited workbook and half the data is missing. It’s not deleted — it’s hidden by leftover AutoFilters the prior preparer forgot to clear. This macro clears every filter on every sheet in under a second and tells you exactly how many it removed. Paste once, run forever.

#Prerequisites & Setup

What you’ll need:

• Excel 2016+ (desktop)
• A workbook with AutoFilters applied — any inherited file where you’re not sure what’s hidden

What the macro does:

• Loops every sheet in ThisWorkbook
• Checks ws.AutoFilterMode — if True, sets it to False
• Reports how many sheets had active filters
• Shows nothing but data that was already there — nothing is modified or deleted

Limitations:

• Does not reset the filter dropdown arrows — the funnel icons remain visible. After running, you’ll see the filter buttons in the header row but no filters are applied. If you want the buttons gone too, use ws.AutoFilter.Range.AutoFilter or Data → Filter to toggle off
• Works on ThisWorkbook — the workbook containing the macro. Store in your Personal Macro Workbook (PERSONAL.XLSB) if you want to run it on any open file
• Does not unhide hidden rows or columns — those are a different mechanism. Use the Row & Column Unhider for that
• Only checks AutoFilterMode, not table filters. Excel tables (ListObjects) with filters active will still report AutoFilterMode = False on the parent sheet

#The Macro

Option Explicit

Sub RemoveAllFilters()
    ' ── Remove All Filters ─────────────────────────────
    ' Loops every sheet in ThisWorkbook and removes
    ' AutoFilters that are hiding rows. Reports how
    ' many sheets had active filters.
    '
    ' Zero input. Non-destructive — the data was already
    ' there, you just couldn't see it.
    ' ────────────────────────────────────────────────────

    ' ── State management ───────────────────────────────
    Application.ScreenUpdating = False

    ' ── Variables ──────────────────────────────────────
    Dim ws As Worksheet
    Dim filterCount As Long

    ' ── Error handling ─────────────────────────────────
    On Error GoTo CleanUp

    filterCount = 0

    For Each ws In ThisWorkbook.Worksheets
        If ws.AutoFilterMode Then
            ws.AutoFilterMode = False
            filterCount = filterCount + 1
        End If
    Next ws

    ' ── Report results ─────────────────────────────────
    If filterCount = 0 Then
        MsgBox "No active filters found on any sheet.", _
               vbInformation, "Remove All Filters"
    Else
        MsgBox "Removed filters from " & filterCount & _
               " sheet(s).", vbInformation, "Remove All Filters"
    End If

CleanUp:
    Application.ScreenUpdating = True

    If Err.Number <> 0 Then
        MsgBox "Error " & Err.Number & ": " & Err.Description, _
               vbCritical, "Macro Error"
    End If
End Sub

#How It Works

#AutoFilterMode vs. AutoFilter — two properties, one job

Excel’s VBA object model gives you two ways to interact with filters on a sheet: ws.AutoFilterMode and ws.AutoFilter. They’re related but not the same.

AutoFilterMode is a simple Boolean — it tells you whether the AutoFilter dropdown arrows are turned on for the sheet. If AutoFilterMode is True, the filter arrows are visible and filters might be active. Setting it to False turns off the filter arrows entirely (which also clears any active criteria).

AutoFilter is the AutoFilter object itself — you can use it to check specific criteria, set filter values, or manipulate individual filter columns.

This macro uses AutoFilterMode because:

1. It’s a one-line check that works instantly — no need to inspect individual filter columns or criteria
2. Setting it to False clears every filter on the sheet at once, regardless of how many columns are filtered or what the criteria were
3. We don’t need to know what was filtered — the user just wants to see all the data

If ws.AutoFilterMode Then
    ws.AutoFilterMode = False
    filterCount = filterCount + 1
End If

Two properties, one outcome: all rows visible, zero configuration.

#Why this is non-destructive

A common hesitation with macros is “what if it deletes something?” This macro doesn’t touch cell values, formulas, or formatting. It doesn’t insert or delete rows. It only changes a display property — AutoFilterMode = False — which shows rows that were already there.

Think of it this way: an AutoFilter is a curtain. The macro opens the curtain. The stage was always there. Nothing was built, torn down, or rearranged.

This is why there’s no confirmation MsgBox before running. There’s no undo risk because there’s nothing to undo — you’re showing data, not changing it.

#The message box is the audit trail

A silent macro that clears filters is confusing — you run it and nothing visibly changes (the rows that were hidden are now visible, but if you were looking at the bottom of the sheet, you might not notice). The message box tells you what happened:

Removed filters from 6 sheet(s).

If you expected 6 and got 4, you know two sheets had their filters cleared by someone else already — or you missed that two sheets weren’t filtered to begin with. If you expected zero and got 8, you now know someone was doing focused work and left filters behind.

#Why there’s no Calculation toggle

Most macros on this blog toggle Application.Calculation = xlCalculationManual to avoid triggering a recalculation cascade on every cell write. This macro doesn’t write to any cells — it only sets ws.AutoFilterMode = False on each sheet. No cell values change, so no formulas need to recalculate. The Calculation toggle is unnecessary overhead.

Application.ScreenUpdating = False is still included. Without it, Excel redraws the sheet after each filter is cleared, which causes visible flickering as rows jump in and out of view. For 6 sheets it’s a minor distraction; for 30 sheets it looks like Excel is having a seizure.

#Adapt It