Excel VBA Pivot Table Filter Criteria: Examples

In Excel VBA, apply PivotTable filter criteria through the relevant PivotField: use CurrentPage for a single report-filter selection, PivotFilters.Add for label or value rules, and item visibility for a specific list of entries. Clear the field’s existing filter first when you want the macro to replace rather than add to the current criteria.

How PivotTable filters work in VBA

A PivotTable field can appear as a report filter, row label, column label, or value field. VBA addresses these fields through the PivotTable’s PivotFields collection. The method you use depends on the filter you mean: selecting one named item is different from asking Excel to show items whose labels contain text or whose totals exceed a threshold.

For example, if a field named Region is in the Filters area, assigning its CurrentPage property selects one region. If Region is in the Rows area, a label filter can keep labels matching a rule. A value filter instead evaluates a measure such as Sum of Sales, and must identify the data field to evaluate.

These examples target desktop Excel for Microsoft 365 on Windows and ordinary, non-OLAP PivotTables. The PivotTable and field captions in VBA must match the workbook’s actual names. A displayed caption can differ from a source column name, particularly after a field has been renamed or a value field has been summarized.

Choose the right kind of filter criteria

One report-filter item: Use CurrentPage when the field is in the Filters area and only one item should be selected.
Label condition: Use a PivotFilter such as caption equals, begins with, or contains when the condition applies to item names.
Value condition: Use a value PivotFilter when the condition concerns a calculation, such as sales greater than a number.
Several specific items: Set the visibility of PivotItems deliberately, or use a slicer/report-filter approach where appropriate. Multi-selection behavior depends on the field and PivotTable type.

Do not confuse a label filter with a value filter. “Region contains West” evaluates the text label. “Sum of Sales is greater than 1,000” evaluates the measure for each item. If the wrong filter type is used, the code may fail or produce a result that does not answer the intended question.

Select one item in a report filter

For a report-filter field, CurrentPage is the direct way to choose one item. The field must be in the Filters area, and the requested text must match an available PivotItem. The following example selects West in a report filter named Region.

Sub FilterRegionToWest()
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim pf As PivotField

    Set ws = ThisWorkbook.Worksheets("Summary")
    Set pt = ws.PivotTables("PivotTable1")
    Set pf = pt.PivotFields("Region")

    pf.ClearAllFilters
    pf.CurrentPage = "West"
End Sub

Change the worksheet, PivotTable, field, and item names to match your workbook. Clearing first removes a prior filter on Region before West is selected. If the field allows multiple selections, CurrentPage is not the right way to select several items; use the field’s item visibility or another suitable filtering interface instead.

Apply label criteria such as equals or contains

Use PivotFilters.Add for conditions based on labels. This example keeps row items whose Region caption equals West. The field must be a label field, such as a row or column field, rather than a data field.

Sub FilterRegionLabelToWest()
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim pf As PivotField

    Set ws = ThisWorkbook.Worksheets("Summary")
    Set pt = ws.PivotTables("PivotTable1")
    Set pf = pt.PivotFields("Region")

    pf.ClearAllFilters
    pf.PivotFilters.Add Type:=xlCaptionEquals, Value1:="West"
End Sub

Other label criteria include constants such as xlCaptionContains and xlCaptionBeginsWith. For example, to keep labels containing “North,” replace the final line with pf.PivotFilters.Add Type:=xlCaptionContains, Value1:="North". These criteria apply to the captions, not to the underlying numeric measure. If more than one filter is applied to the same field, consider how the filters interact and clear old criteria when the new run should start fresh.

Filter by a value or calculated total

A value filter needs both the field being filtered and the data field whose values Excel should test. The next example keeps Region items whose Sum of Sales is greater than 1,000. In the PivotTable, the value field’s caption must be exactly Sum of Sales; if Excel displays a different caption, use that exact caption in the code.

Sub FilterRegionsBySales()
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim salesField As PivotField

    Set ws = ThisWorkbook.Worksheets("Summary")
    Set pt = ws.PivotTables("PivotTable1")
    Set pf = pt.PivotFields("Region")
    Set salesField = pt.DataFields("Sum of Sales")

    pf.ClearAllFilters
    pf.PivotFilters.Add Type:=xlValueGreaterThan, _
        DataField:=salesField, Value1:=1000
End Sub

Excel’s comparison value is 1,000 in the example; it is a threshold, not a claim about any particular workbook’s results. Other value-filter constants include xlValueGreaterThanOrEqualTo, xlValueLessThan, and xlValueBetween. For a between condition, supply both boundaries with Value1 and Value2. Check that the intended summary calculation—Sum, Count, Average, or another calculation—is the one represented by the chosen data field.

Refresh, clear, and replace criteria reliably

Filters can only act on the items and values currently available to the PivotTable. When the source data has changed, refresh the table before applying a criterion so that newly added records and updated totals are included. A refresh does not guarantee that every filter choice remains valid: a requested item may have been removed or renamed.

PivotField.ClearAllFilters clears filters on that field, including its manual filter and label or value filters. It does not mean “clear every field in the entire PivotTable.” Use it on the field you intend to reset. PivotTable.ClearAllFilters is the broader option when the intention is to remove the PivotTable’s filters altogether, so use that only when resetting all criteria is appropriate.

A typical sequence is:

Set references to the correct worksheet, PivotTable, and field.
Refresh the PivotTable if its source data has changed.
Clear the old filter on the target field if the new criterion should replace it.
Apply the report-filter selection or label/value rule.
Check the resulting PivotTable and handle missing items or renamed captions.

If the macro is run repeatedly, clearing the target field before adding a new PivotFilter helps avoid accumulating criteria. If the code should preserve a user’s other filters, do not clear the whole PivotTable as a shortcut.

Common errors and how to diagnose them

Subscript out of range: A worksheet, PivotTable, or collection name does not match the workbook. Confirm spelling and whether the table is on the worksheet referenced by the code.
Item is not available: The filter value may not exist in the refreshed field, may have different spacing, or may be hidden by another condition. Check the PivotField’s visible item captions.
CurrentPage fails: The field may not be in the Filters area, or the requested item may not be available. Use a label filter for row/column labels instead.
Value filter fails: Confirm that the data-field caption exists in pt.DataFields and that the field being filtered is a label field. A value filter compares summarized values, not source labels.
Filter appears to do nothing: The PivotTable may need refreshing, the criterion may match every item, or the selected measure may not be the one shown in the report.

For debugging, step through the macro with the VBA editor and inspect the field captions and PivotTable names. The Immediate window can also help confirm a collection name, but the key is to verify the actual PivotTable structure rather than assume the source column header is still the displayed caption.

Data Model, OLAP, and platform differences

The examples above suit conventional PivotTables. PivotTables based on the Excel Data Model or an OLAP source can behave differently: their fields and members are cube-based, and ordinary PivotItem visibility or report-filter assignments may not work in the same way. For these reports, the appropriate approach can involve cube fields or member unique names. Identify the PivotTable’s source type before adapting standard VBA examples, especially if a method raises an error despite correct captions.

VBA macros run in the desktop Excel application, not in Excel for the web. Excel for the web can display and interact with workbooks containing macros, but it cannot run VBA code. Excel for Mac includes VBA support, though some object-model behavior and available developer tooling can differ from Windows; test macros on the target platform. Save the workbook in a macro-enabled format such as .xlsm if it must retain VBA code.

To make a macro easier to maintain, avoid relying on the currently selected cell or active PivotTable. Explicit worksheet and PivotTable references, as in the examples, make it clearer which report the code will change. Also consider using a descriptive procedure name and constants or clearly named variables for frequently changed filter values.

Safety before running filter macros

A filtering macro changes the workbook’s view and may alter existing criteria. Save a copy of the workbook before running a macro you did not write or have not checked. VBA actions cannot be undone with Ctrl+Z, so the Undo command is not a reliable recovery plan. Keep macros disabled unless you trust the workbook and its source, and review code before enabling it.

If the macro only changes a PivotTable filter, it does not rewrite the source records, but it can still affect what users see, print, or export. If the workbook is shared, make the intended filter state clear and avoid silently removing users’ other selections. A test copy with a small, known data set is a useful way to verify the field names and criteria before applying a macro to an important report.

Frequently Asked Questions

How do I filter a PivotTable by one item in VBA?
For a field in the Filters area, clear its old criteria and set PivotFields("Region").CurrentPage = "West", replacing the names with those in your workbook. The item must exist in that field.

How do I filter a PivotTable for text that contains a word?
Use a label filter on the row or column field: PivotFilters.Add Type:=xlCaptionContains, Value1:="North". Clear prior filters on that field first if the new criterion should replace them.

How do I filter PivotTable values greater than a number?
Use xlValueGreaterThan and provide the appropriate PivotTable data field through the DataField argument. The test applies to summarized values, not to the text label of each item.

Why does CurrentPage not work for my field?
CurrentPage is for selecting a single item in a report-filter field. If the field is placed in Rows or Columns, use a label/value filter or change its layout before using a report-filter selection.

Can VBA filter a PivotTable in Excel for the web?
No. Excel for the web does not execute VBA macros. Run the macro in desktop Excel, then save the workbook if you need to view its updated state online.

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.