Excel VBA Pivot Table Filter Multiple Values at Once

To filter a standard Excel PivotTable to several items with VBA, clear the field’s existing filter and set each PivotItem’s Visible property according to whether it belongs in your keep-list. For a report-filter field, also enable multiple page items; Data Model and other OLAP PivotTables use a different filtering approach.

What filtering multiple values means

A PivotTable field can show one item, several selected items, or all items. For example, a Region field might show East and West while excluding North and South. In the PivotTable interface, that is a multi-select filter. In VBA, the right method depends on the field’s position and on the kind of PivotTable behind the report.

Standard PivotTables, built from a worksheet range or Excel table, expose their field items through the PivotField and PivotItem objects. A macro can loop through those items and hide the ones that are not wanted. A field placed in the Filters area is also called a report-filter or page field. It needs multiple-item selection enabled before it can show more than one selection. A field in Rows or Columns does not use that page-field setting.

“Multiple values” here means multiple field items, such as East and West—not multiple numeric values in a Values area. If the goal is to filter records based on a measure, such as displaying products with sales above a threshold, use a value filter instead.

Filter items manually in the PivotTable

For a one-time change, the interface is usually quicker than writing a macro. In Excel for Microsoft 365 on Windows, open the filter dropdown for the field. For a report-filter field, use its dropdown above the PivotTable; for a row or column field, use the dropdown beside its label. Select the Select Multiple Items checkbox if it appears, then tick the items to retain and clear the items to exclude.

The exact checkbox depends on the field and layout. Row and column label menus normally show item checkboxes; report filters may require explicitly turning on Select Multiple Items. If you only need one choice, leave multi-select off and choose that item. To remove the filter, choose the field’s clear-filter option, where available.

Excel for the web supports many PivotTable interactions, but it does not run VBA macros. To execute VBA, open the workbook in the desktop Excel application. Excel for Mac also supports VBA, although ribbon placement and some PivotTable controls can differ from Windows; the VBA object model code below is intended for desktop Excel and should be checked against the workbook’s PivotTable type.

VBA example for a standard PivotTable

This example keeps East and West in a field named Region. It assumes a worksheet named Pivot, a PivotTable named PivotTable1, and a standard, non-OLAP PivotTable. Change those names and the item names to match your workbook. The field can be in Rows, Columns, or Filters; the macro enables multiple selections when it is a report-filter field.

Sub FilterPivotToEastAndWest()
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim pi As PivotItem
    Dim keepItem As Boolean
    Dim failure As String

    On Error GoTo HandleError

    Application.ScreenUpdating = False
    Set ws = ThisWorkbook.Worksheets("Pivot")
    Set pt = ws.PivotTables("PivotTable1")
    Set pf = pt.PivotFields("Region")

    pt.ManualUpdate = True
    pf.ClearAllFilters

    If pf.Orientation = xlPageField Then
        pf.EnableMultiplePageItems = True
    End If

    For Each pi In pf.PivotItems
        Select Case CStr(pi.Name)
            Case "East", "West"
                keepItem = True
            Case Else
                keepItem = False
        End Select

        If Not keepItem Then pi.Visible = False
    Next pi

    pt.ManualUpdate = False
    pt.RefreshTable
    Application.ScreenUpdating = True
    Exit Sub

HandleError:
    failure = Err.Description
    On Error Resume Next
    If Not pt Is Nothing Then pt.ManualUpdate = False
    Application.ScreenUpdating = True
    MsgBox "Filter was not applied: " & failure, vbExclamation
End Sub

The loop leaves East and West visible and hides every other item. ClearAllFilters removes the field’s previous filter first, so the macro does not accidentally preserve an earlier selection that conflicts with the new one. ManualUpdate temporarily postpones PivotTable updates while the macro changes the field, and RefreshTable asks Excel to refresh the displayed report afterward.

Both requested items need to exist in the field, and at least one item must remain visible. If an item is absent, this example will still hide other items, but the final result may not match the intended two-item selection. For more defensive automation, check that every requested item exists before changing visibility and stop with a clear message if one is missing.

Change the keep-list or field position

To retain different items, edit the Case line. For instance, Case “Open”, “Pending”, “Escalated” keeps three status items. The text must match the PivotItem names in the field. Names can differ from what you expect because of trailing spaces, spelling, capitalization, or source-data cleanup. A quick way to inspect them is to step through the PivotItems collection in the VBA editor or print each item’s Name to the Immediate window.

If the field is in the Filters area, the code sets EnableMultiplePageItems to True. This setting permits more than one report-filter item to be selected; it does not itself choose the items. The visibility loop performs the selection. If the field is a row or column field, the macro skips this setting because it is not a report filter.

The example deliberately uses a fixed keep-list because it is easy to understand and maintain. A more flexible macro could read the requested names from worksheet cells, but it should still validate those names before hiding anything. Avoid hiding every item: Excel needs at least one visible item in a PivotField, and attempting to hide the last visible item can raise an error.

Data Model and OLAP PivotTables

A PivotTable built from the Data Model, an OLAP cube, or another OLAP source may not behave like a standard PivotTable. In particular, looping through PivotItems and assigning Visible can fail or be unsupported for OLAP fields. Those fields are commonly filtered with the PivotField.VisibleItemsList property instead, using the field’s unique member names rather than merely its displayed captions.

For example, an OLAP field may expose a member identifier that looks quite different from the visible label East. The identifier belongs to the cube’s hierarchy and must be obtained from that source; it is not safe to invent it from the caption. VisibleItemsList accepts an array of member unique names for the items to display, but the correct names and behavior depend on the cube and hierarchy. If you are automating a Data Model PivotTable, first confirm that the field is OLAP-backed and inspect its member names rather than copying the standard-PivotTable loop unchanged.

This distinction also explains why a macro can work on one PivotTable and fail on another even when both look similar. The visual layout does not reveal the underlying cache type. A workbook may contain a worksheet-based PivotTable, a Data Model PivotTable, or both.

Common errors and how to prevent them

• Wrong worksheet, PivotTable, or field name: The strings in the macro must match the workbook objects. PivotTable names are not necessarily the same as worksheet names or the title displayed above the report.
• Item name does not match: The source may contain unexpected spaces or different text. Inspect the actual PivotItem.Name values before relying on a hard-coded keep-list.
• Only one report-filter item is selected: Set EnableMultiplePageItems to True for a page field before applying multiple selections. This is not needed for a row or column field.
• Attempting to hide the last visible item: Always keep at least one item visible. Confirm that the keep-list contains valid items before changing visibility.
• Filters seem to return after refresh: A PivotTable refresh can rebuild its displayed items from the source or cache. Check the source data, refresh settings, and whether new items have appeared since the filter was first applied.
• The workbook uses OLAP or the Data Model: Use the OLAP-compatible member-list approach rather than assuming standard PivotItem visibility will work.

For troubleshooting, run the macro on a copy of the workbook and use the VBA editor’s Debug button when an error occurs. Check the highlighted line and verify the field orientation and PivotTable source. Keep error handling in production code so Excel’s screen updating and manual-update state are restored if a change fails.

Refresh, persistence, and safe macro use

A filter selection is stored with the PivotTable, but a refresh can change the available items when source data changes. If a macro runs after refresh, apply the filter after the PivotTable has finished updating so the item collection reflects the current cache. If the macro runs before refresh, newly added values may not yet be available to the loop.

Save a copy of the workbook before running a macro. VBA actions cannot be undone with Ctrl+Z, and a macro can change more than the visible selection if it is pointed at the wrong field. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) if you need to retain VBA code. Only enable macros in files you trust.

When distributing a workbook, remember that recipients need desktop Excel with macros enabled to run the code. Excel for the web can display many macro-enabled workbooks, but it does not execute VBA. If users need an interactive filter without macros, a normal PivotTable dropdown or slicer is often easier to share.

When a slicer is a better choice

If people need to change the selection repeatedly, add a slicer instead of running a macro. Select the PivotTable, then use PivotTable Analyze > Insert Slicer and choose the field. Users can click multiple slicer buttons; hold Ctrl while selecting additional items on Windows. Slicers make the active selection visible on the sheet and reduce the need to open dropdown menus.

A slicer is a user interface, not a replacement for VBA in every situation. A macro is useful when the same selection must be applied automatically, when a button should trigger a repeatable report setup, or when a process updates several PivotTables. A slicer is preferable when the workbook’s audience should choose items interactively without editing code. Slicers can also connect to multiple PivotTables that share a compatible data source, using the slicer’s Report Connections or PivotTable Connections option.

Frequently Asked Questions

Can I filter a PivotTable to multiple items with CurrentPage?
Not for a multi-item selection. CurrentPage is used to set one page-field item. For a standard PivotTable, enable multiple page items and set item visibility; for an OLAP PivotTable, use the appropriate member-list method.

Does EnableMultiplePageItems work for row fields?
No setting is needed for row or column fields. EnableMultiplePageItems applies to report-filter fields in the Filters area. Use the field’s PivotItems visibility to select several items in a standard PivotTable.

Why does my macro say the Visible property cannot be set?
Common causes include an OLAP or Data Model PivotTable, an attempt to hide the last visible item, or an invalid filtering state. Confirm the source type, keep at least one item visible, and use VisibleItemsList for OLAP fields when appropriate.

Can I select multiple items in Excel for the web?
You can interact with supported PivotTable filters in Excel for the web, but VBA macros do not run there. Open the workbook in desktop Excel to execute the macro, or use a slicer or dropdown for a macro-free workflow.

Will a PivotTable filter stay after refreshing?
It may stay if the selected items are still present, but refresh can add, remove, or update items and affect the result. For repeatable behavior, refresh the PivotTable first, then have the macro apply and validate the desired selection.

Related

• Excel Named Ranges List: Find, View, and Manage Defined Names
• Excel Named Range Shortcut: Create, Edit, and Use Names
• Excel Page Break Dotted Lines: How to Move Them in Print

More in VBA & macros

• Excel Macro Commands List: Common VBA Actions ExplainedAn Excel macro command is a Visual Basic for Applications (VBA) instruction that tells Excel what to do,…
• Excel VBA Find Value in Range: Examples and TipsTo find a value in an Excel range with VBA, use the range’s Find method, then check whether…
• Excel VBA Find a Substring in a StringIn Excel VBA, use InStr to find a substring inside a string: it returns the position of the…
• Excel VBA Get Array Dimensions: Bounds, Rank, and SizeIn Excel VBA, use LBound(array, dimension) and UBound(array, dimension) to get an array’s lower and upper bounds; its…
• Access VBA Date Conversion: Parse, Format, and ValidateIn Access VBA, use CDate to convert a value VBA already recognizes as a date, DateSerial to build…
• Excel VBA Freeze Worksheet: Freeze Panes or Lock CellsIn Excel VBA, use ActiveWindow.FreezePanes to keep rows or columns visible while you scroll; use worksheet protection when…

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.