🚀The world's best VBA AI has evolved. ExcelMaster is now an autonomous Agent.Read more →
Back to Blog

VBA AutoFilter in Excel — Filter Rows in Code (and Why Hidden Isn't Deleted)

|

VBA AutoFilter in Excel — Filter Rows in Code (and Why Hidden Isn't Deleted)

TL;DR — AutoFilter hides the rows that do not match your criteria — it does not remove them. Those hidden rows are still in the range, still counted by SUM, and still copied by a plain .Copy. To read, copy, or delete only what is visible, you must route through .SpecialCells(xlCellTypeVisible). And watch the toggle trap: calling .AutoFilter with no arguments flips filtering on or off, so a macro that "sets a filter" can turn the filter off when it runs a second time. Clear state first with .AutoFilterMode = False.

' Filter a table to Region = "West", then act on the VISIBLE rows only.
Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Sales")
ws.AutoFilterMode = False                      ' clear any leftover filter first

With ws.Range("A1").CurrentRegion              ' the whole table incl. header
    .AutoFilter Field:=2, Criteria1:="West"    ' Field is 1-based WITHIN the range
    ' visible data rows only (skip the header with Offset(1)):
    .Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Copy _
        Destination:=Worksheets("West").Range("A1")
End With
ws.AutoFilterMode = False                       ' remove the filter when done

AutoFilter is the fastest way to narrow a table to the rows you care about, and it is the engine behind the quickest bulk-delete in Excel VBA. But it fools people because it looks like it removed rows when all it did was hide them. Almost every AutoFilter bug is a version of that one misunderstanding, so that is where this guide starts.

What you'll learn

  • The mental model — AutoFilter is a view, not a delete; hidden rows are still there
  • Why SUM and .Copy still include filtered-out rows, and how SpecialCells(xlCellTypeVisible) fixes it
  • Setting criteria — single values, comparison operators, two conditions, and "in a list"
  • The toggle trap — why a no-argument .AutoFilter call turns filtering off
  • The professional filter-then-delete pattern for removing thousands of rows fast
  • AutoFilter vs the FILTER function vs Advanced Filter — which one to reach for

The mental model: a filter is a view, not a delete

When you apply a filter, Excel does exactly one thing: it sets the hidden property on every row that does not match. The data does not move. Nothing is removed. You are looking at the same range through a stencil that blocks some rows from view.

That is the whole idea, and it has a consequence people find surprising the first time: everything that operates on the range still sees the hidden rows. =SUM(B2:B1000) adds the filtered-out numbers. Range("B2:B1000").Copy copies all of them, hidden or not. A For Each cell loop visits every one. The filter changed what you see; it did not change what the range contains.

So the mental model is a question you ask before every operation on a filtered table: do I want all the rows, or only the visible ones? If the answer is "only visible," you cannot use the range directly — you have to ask Excel for the visible subset explicitly. That is the one habit that makes AutoFilter reliable.

The rule that matters most: reach visible rows through SpecialCells

To touch only the rows the filter left showing, you convert the range to its visible cells:

Dim visible As Range
On Error Resume Next     ' xlCellTypeVisible raises 1004 if nothing is visible
Set visible = ws.Range("A1").CurrentRegion.Offset(1) _
                  .SpecialCells(xlCellTypeVisible)
On Error GoTo 0

If Not visible Is Nothing Then
    ' visible is a MULTI-AREA range - the filtered rows are non-contiguous
    MsgBox "Visible rows: " & visible.Rows.Count
End If

Two things to internalise here. First, SpecialCells(xlCellTypeVisible) returns a multi-area range — because the visible rows are scattered with hidden ones between them. Loop it with For Each area In visible.Areas when you need to process blocks, or use .EntireRow operations that handle areas for you. Second, it raises error 1004 when nothing is visible (a filter that matched zero rows), so guard it with On Error. Skip SpecialCells and operate on the raw range, and you are silently working on the hidden rows too — the single most common AutoFilter mistake.

Setting criteria: values, operators, and lists

AutoFilter takes a Field (1-based within the filtered range, not the sheet's column letter) and one or two criteria:

With ws.Range("A1").CurrentRegion
    ' exact value
    .AutoFilter Field:=2, Criteria1:="West"

    ' comparison - note the operator is inside the string
    .AutoFilter Field:=3, Criteria1:=">1000"

    ' two conditions on one field (between 100 and 500)
    .AutoFilter Field:=3, Criteria1:=">=100", Operator:=xlAnd, Criteria2:="<=500"

    ' "in a list" - an array plus xlFilterValues
    .AutoFilter Field:=2, Criteria1:=Array("West", "East"), Operator:=xlFilterValues
End With

The trap that catches everyone once: Field counts from the left edge of the range you called .AutoFilter on, not from column A of the sheet. If your table starts in column C, Field:=1 is column C. Get that offset wrong and you filter the wrong column with no error at all.

The toggle trap: a bare AutoFilter turns filtering off

.AutoFilter called with no arguments is a toggle — it switches the filter arrows on if they are off, and off if they are on. That makes it dangerous inside a macro that might run more than once:

ws.Range("A1").CurrentRegion.AutoFilter    ' run once: arrows ON. run again: arrows OFF.

The same statefulness bites in a subtler way: a filter left over from a previous run — or from a user filtering by hand — silently changes what "visible" means the next time your code reads the range. Do not trust the inherited filter state. Start by clearing it, and clear it again when you finish:

ws.AutoFilterMode = False   ' remove any existing filter - clean slate
' ... apply your filter, do your work ...
ws.AutoFilterMode = False   ' leave the sheet as you found it

Checking If ws.AutoFilterMode Then tells you whether a filter is currently active. Making your macro state-independent — clear, filter, work, clear — is what stops the "worked once, broke the second time" class of bug.

The pattern worth memorising: filter, then delete the visible rows

This is why AutoFilter earns its place in serious code. To delete thousands of rows matching a condition, filtering and deleting the visible cells is dramatically faster than looping — Excel does the matching, and one .Delete removes them all:

With ws.Range("A1").CurrentRegion
    .AutoFilter Field:=5, Criteria1:="Cancelled"
    ' delete visible data rows - Offset(1) skips the header, or you delete your titles
    On Error Resume Next
    .Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    On Error GoTo 0
End With
ws.AutoFilterMode = False

The .Offset(1) is the gotcha: without it, the header row is visible too and gets deleted along with the data. This filter-then-delete approach pairs naturally with the delete techniques in VBA Delete Rows — and it is the approach I reach for over a backwards loop whenever the match count is large.

AutoFilter vs FILTER function vs Advanced Filter

Three tools share the word "filter" and solve different problems:

  • AutoFilter hides non-matching rows in place. Use it when you want to see, copy, or delete a subset of an existing table. It is a view over the original.
  • The FILTER() worksheet function spills a new array of matching rows somewhere else, live and recalculating. Use it when you want a formula-driven list that updates itself — no macro needed.
  • Advanced Filter (Range.AdvancedFilter) can copy matches to another location or extract unique values with a criteria range. Use it for complex, multi-condition extracts.

The judgment: for "narrow this table so I can act on the matches," AutoFilter is right. If you catch yourself wanting a live, self-updating list of matches in another place, stop writing a macro — that is the FILTER() function's job, not AutoFilter's.

How ExcelMaster helps

AutoFilter's traps are all quiet ones: summing a filtered range and including the hidden rows, copying and getting everything, deleting without SpecialCells and hitting the wrong cells, the Field offset, the toggle that flips your filter off on the second run. None raise an error — they just act on rows you thought were gone.

ExcelMaster lets you say what you want the table to show. Describe "keep only the West region rows over 1000" or "delete every Cancelled order," and it clears stale filter state first, gets the Field index right, routes through SpecialCells(xlCellTypeVisible) for anything that touches only visible rows, and backs up the sheet before deleting. You keep the workbook and the code; you skip the part where a hidden row sneaks into your total.

Frequently asked questions

Does VBA AutoFilter delete the rows it filters out?

No. AutoFilter only hides non-matching rows — they remain in the range, still counted by SUM, still copied by .Copy, and still visited by loops. To work on only the visible rows you must go through .SpecialCells(xlCellTypeVisible). To actually remove rows, filter and then delete the visible cells.

Why does SUM still include my filtered-out rows in VBA?

Because filtering hides rows, it does not remove them, and SUM adds hidden values. Use SUBTOTAL(109, range) instead of SUM to total only visible rows, or in VBA sum .SpecialCells(xlCellTypeVisible). This is the clearest sign that a filter is a view, not a delete.

How do I filter by two conditions in VBA AutoFilter?

Pass Criteria1, an Operator, and Criteria2. For a range use xlAnd (Criteria1:=">=100", Operator:=xlAnd, Criteria2:="<=500"); for either-or use xlOr. For "value in a list" pass an array to Criteria1 with Operator:=xlFilterValues.

Why does my AutoFilter turn off when the macro runs again?

Because calling .AutoFilter with no arguments is a toggle — it switches filtering on if off and off if on. Run it twice and the filter disappears. Make the macro state-independent: call ws.AutoFilterMode = False to clear first, then apply your filter with explicit arguments.

What is the fastest way to delete rows by condition in VBA?

Apply AutoFilter to match the rows you want gone, then delete the visible cells in one call: .Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete (the Offset(1) skips the header). Excel does the matching and a single delete removes them all, which is far faster than looping row by row.

Tested in

Tested in: Excel 365 (Windows 11), VBA 7.1 — last verified 2026-08-08.

Related guides: VBA Find · VBA Sort · VBA Delete Rows · VBA Last Row · VBA Range