Excel VBA: Filter a PivotTable by Field or Value

Yes. In Excel VBA, filter a PivotTable by setting a report-filter field’s CurrentPage, applying a label or value filter with PivotFilters.Add2, or changing which PivotItems are visible. The right method depends on whether you want to select a field item, filter by its caption, or filter using summarized values.

What a PivotTable filter does

A PivotTable filter limits which source records contribute to the displayed summary. The filter can affect a report-filter field, such as Region, or a field placed in the Rows or Columns area, such as Product. Those placements matter in VBA: CurrentPage is for a report-filter field, while label and value filters are generally applied to row or column fields.

For example, a sales PivotTable could show only the West region, list only product names beginning with “A,” or show products whose summed sales exceed $1,000. These are different filtering operations, even though each changes what appears in the PivotTable. A normal worksheet AutoFilter is not the same thing: it filters a range or table, not the PivotTable’s field items and summaries.

Before automating a filter, identify the worksheet, PivotTable, and field names as they appear in Excel’s object model. A field caption or PivotTable name that differs by even a character can cause a macro to stop with an error. The source column heading and the field caption are often the same, but renamed fields may have a different caption.

Prepare the workbook and identify the field

In the example below, the workbook contains a worksheet named Summary, a PivotTable named SalesPivot, and a field called Region. To find a PivotTable’s name, click inside it and look for the PivotTable Name box on the PivotTable Analyze tab. Field names appear in the PivotTable Fields pane; use the caption displayed there when referring to the field in VBA.

To add and run the macro in Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, choose Insert > Module, paste the procedure into the module, and run it with the cursor inside the procedure or from the Macros dialog. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) if it needs to retain VBA code. On Mac, Option+F11 or Fn+Option+F11 may open the editor depending on keyboard settings; VBA is supported in desktop Excel for Mac, while Excel for the web cannot run or edit VBA macros.

Set one report-filter item

Use CurrentPage when the field is in the PivotTable’s Filters area and you want one item selected. The following example moves Region to that area, clears existing filters on the field, and selects West:

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

    ' Find the PivotTable and its Region field.
    Set ws = ThisWorkbook.Worksheets("Summary")
    Set pt = ws.PivotTables("SalesPivot")
    Set pf = pt.PivotFields("Region")

    ' Place Region in the report-filter area and select one item.
    pf.Orientation = xlPageField
    pf.ClearAllFilters
    pf.CurrentPage = "West"
End Sub

Changing Orientation to xlPageField is optional if Region is already in the Filters area. If it is currently in Rows or Columns, moving it changes the PivotTable layout; omit that line if preserving its present placement is important. The item “West” must exist in the field after the PivotTable has been refreshed. If it does not, assigning it will fail rather than create a new item.

ClearAllFilters clears filters on that field before the new selection is applied. It does not mean “clear every filter in the entire workbook,” but it can remove a user’s existing label, value, or item selection on Region. Use it only when replacing the field’s current filtering is intended.

Apply label and value filters

A label filter tests the field item’s text, such as a product caption beginning with a letter. A value filter tests an aggregated result, such as summed sales for each product. For a value filter, specify the PivotTable’s data field so Excel knows which summary to evaluate. This example is for a conventional, non-OLAP PivotTable with Product in Rows and a data field captioned “Sum of Sales”:

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

    ' Find the row field and the summarized sales field.
    Set ws = ThisWorkbook.Worksheets("Summary")
    Set pt = ws.PivotTables("SalesPivot")
    Set pf = pt.PivotFields("Product")
    Set salesField = pt.DataFields("Sum of Sales")

    ' Replace existing Product filters with a value condition.
    pf.ClearAllFilters
    pf.PivotFilters.Add2 Type:=xlValueIsGreaterThan, _
        DataField:=salesField, Value1:=1000
End Sub

The threshold here is 1,000 in the same units as the summarized sales field. A product with exactly 1,000 is excluded because the condition is greater than, not greater than or equal to. If your data field is captioned differently—for example, “Sales Total”—change the name passed to DataFields. Excel versions and PivotTable sources can differ in which filter types they support, so test the code on a copy, especially for Data Model or OLAP PivotTables.

For a text-based condition, use a label filter type such as xlCaptionBeginsWith and supply the caption text as Value1. The precise type should match the intended test: “equals,” “contains,” and “begins with” produce different results. Do not use a value-filter type for text captions or assume a label filter evaluates the summarized sales amount.

Select several items or hide individual items

CurrentPage selects one report-filter item; it is not a multi-select mechanism. For a report filter that allows several choices, set the field’s EnableMultiplePageItems property to True, then manage the visible items. In the Excel interface, the field’s filter menu must also allow multiple selections. When changing items in VBA, keep at least one item visible: a PivotTable field cannot be left with every item hidden.

For a row or column field, individual items can be hidden by setting a PivotItem’s Visible property to False. This is useful for a short, known exclusion list, but can be fragile if the source data changes. A newly added item may remain visible, and trying to hide the last visible item raises an error. For large or changing lists, a label filter, value filter, slicer, or a refreshed source table is usually easier to maintain than a long loop that hides items one at a time.

OLAP and Data Model PivotTables have additional restrictions, and some item-selection techniques that work with ordinary PivotTables do not work the same way against cube fields. Do not assume that a non-OLAP example using PivotItems or PivotFilters.Add2 will transfer unchanged. Confirm the source type and test the intended filter in the desktop version of Excel you use.

Refresh order and keeping filters reliable

A filter can only select items currently available to the PivotTable. If a new Region or Product was added to the source, refresh the PivotTable before selecting it. For a single PivotTable, VBA can call pt.RefreshTable; for multiple PivotTables that share a cache, refresh strategy may depend on how the workbook is built. If a PivotTable uses a connection or the Data Model, refreshing may take longer or depend on connection settings.

Refresh timing matters when a macro runs after importing data. A reliable sequence is to update the source, refresh the relevant PivotTable or cache, apply the filter, and then inspect the result. If the filter runs before the refresh has completed, an item that exists in the new source may still be unavailable. For asynchronous connections, code may need to wait for the refresh to finish; simply adding a brief pause is not a dependable substitute for checking completion.

Also check whether the source range includes all new rows. Refreshing a PivotTable does not necessarily expand a fixed source range to include records appended outside it. An Excel Table is often a better source because it expands as rows are added. This is a source-range issue, not a VBA filter issue, and it can make a correct macro appear to ignore new data.

Common errors and how to diagnose them

“Subscript out of range”: Check the worksheet and PivotTable names, including spaces and spelling. The name in the PivotTable Analyze tab is not always the same as the worksheet tab name.
“Unable to get the PivotFields property” or a missing field: Confirm the field caption and that the PivotTable contains that field. A renamed caption or a changed source heading can break a hard-coded reference.
CurrentPage assignment fails: Confirm the field is in the report-filter area, the selected item exists, and the field is not in a source configuration that restricts this operation.
A value filter does not behave as expected: Confirm the data field is the intended aggregation, such as Sum rather than Count, and that the threshold uses the same units as the summary.
Filters disappear after refresh: Inspect PivotTable options, source changes, and other macros. Refreshing can change available items, while code that calls ClearAllFilters deliberately removes prior filtering.

When diagnosing a failure, run the macro one statement at a time in the Visual Basic Editor with F8 on Windows. On Mac, the editor also supports stepping through code, though shortcuts can differ by keyboard settings. Inspect the actual object names and field captions rather than guessing. Avoid suppressing every error with On Error Resume Next; it can hide a failed assignment and leave the PivotTable in an unexpected state.

VBA filters compared with the interface and slicers

Use the filter dropdown in the PivotTable when a person needs to make a one-off choice. Use VBA when the same selection should be applied predictably after a button click, data import, or report refresh. A macro is also useful when several PivotTables need coordinated settings, but it adds maintenance: names, captions, source structure, and macro permissions all need to remain valid.

Slicers are a visual alternative for interactive filtering. In desktop Excel, click a PivotTable and choose PivotTable Analyze > Insert Slicer, then select the field. A slicer makes available choices visible and can be connected to compatible PivotTables through Report Connections. It is often easier for workbook users than running a macro, but it is not a VBA filter command and may not suit an automated export workflow.

For repeatable reports, decide whether the desired outcome is a filtered PivotTable or a filtered copy of the underlying records. PivotTable filtering changes the summary view; it does not delete source rows. If a macro is intended to remove data, that is a separate, higher-risk operation and should not be confused with hiding PivotTable items.

Macro safety and workbook sharing

Save a copy of the workbook before running a macro. VBA actions cannot be undone with Ctrl+Z, and a procedure can alter field placement or clear user-applied filters even when it does not change source records. Review the code and test it on sample data before using it in a report others depend on.

Only enable macros in workbooks from sources you trust. Excel’s macro security controls may block code downloaded from the internet or received by email. A recipient using Excel for the web can view the workbook but cannot run its VBA; they need desktop Excel to execute the macro. If a workbook must work without macros, consider a manually operated PivotTable filter or slicer instead.

Frequently Asked Questions

Can VBA filter a PivotTable by a cell value?
Yes. Read the cell’s value in VBA and use it as the target for a suitable field filter, such as assigning it to CurrentPage for a report-filter field. The value must match an available PivotItem, and the field must support that filter method.

How do I clear a PivotTable filter with VBA?
Call ClearAllFilters on the relevant PivotField to clear that field’s filters. To clear filters on every field, loop through the PivotTable’s PivotFields and clear each one, taking care that doing so removes intentional user selections too.

Can I filter a PivotTable by a number in the summary?
Yes. Apply a value filter to a row or column field and identify the summarized data field, as in the sales example. The condition applies to the aggregation for each item, not to individual source rows.

Why does CurrentPage not work on my field?
CurrentPage is intended for a report-filter field with one selected item. Check the field’s orientation, exact item caption, and PivotTable source type. For a row field or a multi-item selection, use a different filtering approach.

Can Excel for the web run a PivotTable VBA filter?
No. Excel for the web does not run VBA macros. Use desktop Excel for Windows or Mac to execute the code, or apply the filter with the PivotTable controls or a slicer.

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.