Filter an Excel PivotTable With VBA: Fields, Items, and Code

To filter an Excel PivotTable with VBA, reference the PivotTable and field, clear any existing filter, then set the desired page-field item or control which PivotItems are visible. For example, a report-filter field named Region can be set to West with pf.CurrentPage = "West"; the examples below show the setup and alternatives for selecting more than one item.

How PivotTable filtering works in VBA

A PivotTable is an Excel object with fields, items, and its own filter methods. VBA can target a particular PivotTable, then change a field’s filter without changing the underlying source data. This differs from applying AutoFilter to a worksheet range: a worksheet filter hides source rows, while a PivotTable filter controls which categories or records contribute to the summarized view.

The main objects used in these examples are:

PivotTable identifies the report to change.
PivotField identifies a source field such as Region, Product, or Order Date.
PivotItem represents an item in a field, such as West or East.

A field’s location matters. A field placed in the Filters area is a report filter, and CurrentPage is convenient when you want one item selected. For a field in Rows or Columns, you will usually show or hide individual items. Value filters, label filters, date filters, and slicers are related, but they use different PivotTable features and are not interchangeable with the simple item-selection examples.

Prepare the workbook and identify the PivotTable

These instructions target desktop Excel for Microsoft 365 on Windows. Save a macro-enabled copy as an .xlsm file. To open the VBA editor, use Alt+F11 on Windows, or choose Developer > Visual Basic. If the Developer tab is missing, enable it through File > Options > Customize Ribbon. On Mac, use Tools > Macro > Visual Basic Editor; the keyboard shortcut can depend on the keyboard’s function-key settings. Excel for the web can display a workbook containing macros, but it cannot run or edit VBA macros.

Before running any macro, save a separate copy of the workbook. VBA changes cannot be undone with Ctrl+Z, so a backup is the practical way to restore the previous state. The examples use a worksheet named Summary, a PivotTable named PivotTable1, and a source field named Region. Replace those names with the names in your workbook. The PivotTable name is visible when you select the PivotTable and open PivotTable Analyze > PivotTable Name.

Filter a report-filter field to one item

This macro places Region in the Filters area and selects West. If Region is already in that area, assigning its orientation again is unnecessary; remove that line if you do not want the macro to move the field from its current location.

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

    ' Update these names to match the workbook.
    Set ws = ThisWorkbook.Worksheets("Summary")
    Set pt = ws.PivotTables("PivotTable1")
    Set pf = pt.PivotFields("Region")

    pf.ClearAllFilters
    pf.Orientation = xlPageField
    pf.EnableMultiplePageItems = False
    pf.CurrentPage = "West"
End Sub

ClearAllFilters removes existing filters from that field before the new selection is applied. CurrentPage works for a page field when selecting one item. The item text must match a PivotItem in the field; if the source contains West Coast rather than West, use the exact displayed item name. If the field is not a report filter, assigning CurrentPage may fail, so use item visibility or move the field to the Filters area first.

Show several items in a row or column field

For a Rows or Columns field, you can keep a list of items visible and hide the rest. The code below clears the field’s current filter, checks that at least one requested item exists, makes all items visible, and then hides items that are not in the requested list. Checking first helps avoid an error caused by trying to hide every item.

Sub ShowSelectedRegions()
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim pi As PivotItem
    Dim keep As Variant
    Dim found As Boolean
    Dim i As Long

    Set ws = ThisWorkbook.Worksheets("Summary")
    Set pt = ws.PivotTables("PivotTable1")
    Set pf = pt.PivotFields("Region")
    keep = Array("West", "Central")

    pf.ClearAllFilters

    ' Stop if none of the requested items exists.
    For Each pi In pf.PivotItems
        For i = LBound(keep) To UBound(keep)
            If StrComp(pi.Name, CStr(keep(i)), vbTextCompare) = 0 Then found = True
        Next i
    Next pi

    If Not found Then
        MsgBox "No requested Region item was found."
        Exit Sub
    End If

    ' Reveal all items before applying the selection.
    For Each pi In pf.PivotItems
        pi.Visible = True
    Next pi

    ' Hide items that are not in the keep list.
    For Each pi In pf.PivotItems
        found = False
        For i = LBound(keep) To UBound(keep)
            If StrComp(pi.Name, CStr(keep(i)), vbTextCompare) = 0 Then found = True
        Next i
        If Not found Then pi.Visible = False
    Next pi
End Sub

The values in keep are examples; edit them to match the PivotTable’s item captions. This pattern is suitable for an ordinary, non-OLAP PivotTable. Data Model and OLAP-based PivotTables can behave differently, and Excel may reject changes to individual item visibility. In those cases, use the PivotField filter methods appropriate to that source, or control the report with a slicer or connected filter instead.

Use multiple selections in a report filter

A report filter can also allow more than one selected item. Set EnableMultiplePageItems to True for the page field, then apply the desired selection. In practice, the visible-item approach above is often easiest to understand, but for a report-filter field you must enable multiple selection first. The field still needs to be in the Filters area, and the items must exist in the PivotTable cache.

Multiple selection can make a report less obvious to readers: the filter box may show that multiple items are selected without naming all of them in the visible cell. Consider whether a row-field filter, a slicer, or a clearly labeled selection cell would make the report’s state easier to interpret. A macro should also set the intended state each time it runs rather than assuming a user has not changed the filter manually.

Clear filters, refresh data, and repeat the process

To reset a field before applying a new choice, use pf.ClearAllFilters. It clears that field’s manual selection and its label, value, or date filter. It does not remove filters from every other field in the PivotTable. To clear all fields, loop through the PivotFields collection and call ClearAllFilters on each one, taking care with special fields or OLAP reports.

Refreshing is a separate action. If the source data has changed, refresh the PivotTable before applying a filter to newly added items. You can call pt.RefreshTable after setting the PivotTable reference. This refreshes the report from its cache or source connection; it does not automatically fix a wrong source range or guarantee that a new record is included in the cache. For a table-backed source, adding records to the Excel table and refreshing is generally more reliable than relying on a fixed range.

For repeated use, put the macro in a standard module and call it from a button, a worksheet event, or another procedure. A button on the report sheet lets readers deliberately apply the filter. Avoid triggering a refresh or filter change on every cell edit unless there is a clear need: frequent events can make a workbook slow and can cause confusing changes while someone is working.

Choose the right method for the filter

Use CurrentPage when a report-filter field should show exactly one item. Use PivotItem.Visible when the requirement is a known set of labels in a normal PivotTable. For “greater than,” “begins with,” date-period, or Top 10 conditions, use the PivotTable’s value, label, date, or Top 10 filter features rather than trying to build a long list of item names. These filters depend on the field type and, for value filters, the measure being summarized.

If a person needs to choose values interactively, a slicer is often a better interface than a macro. Select the PivotTable, then use PivotTable Analyze > Insert Slicer and choose the fields. A slicer makes the current selection visible and can be connected to compatible PivotTables that share a cache. VBA is more useful when the selection follows a repeatable rule, comes from a control cell, or must be part of a larger automated report routine.

Common errors and practical safeguards

Subscript out of range: The worksheet or PivotTable name in the code does not match the workbook. Confirm spelling and spaces, and check that the macro uses the workbook containing the report.
Unable to set the CurrentPage property: The field may not be in the Filters area, may allow multiple page items, or may not contain the requested item. Confirm its location and exact item caption.
Unable to set the Visible property: The code may be targeting an OLAP/Data Model PivotTable, or it may be trying to hide every item. Check the source type and ensure at least one item remains visible.
New items are not available: Refresh the PivotTable and verify that its source includes the new records. A PivotTable cannot filter an item absent from its cache.
The filter changes the wrong report: PivotTable names are local to a worksheet, and a workbook may contain several similarly named reports. Qualify the worksheet and PivotTable explicitly as in the examples.

Macros may be blocked by Excel’s security settings. Enable only macros from workbooks you trust, and do not lower security settings just to run unfamiliar code. If a routine changes several fields, test it on a copy and make each intended change explicit; a filter macro can alter what a report displays even though it does not delete source records.

Frequently Asked Questions

Can VBA filter a PivotTable by a cell value?
Yes. Read the cell’s value into a variable, validate that it matches an available PivotItem, then assign it to CurrentPage for a single-selection report filter. For a row or column field, use the value to decide which PivotItems remain visible.

Does filtering a PivotTable with VBA delete rows from the source?
No. A PivotTable filter changes the displayed summary and which items contribute to it; it does not delete the original worksheet records. Keep the distinction clear if a macro also edits or deletes source data.

Why does CurrentPage give an error?
CurrentPage is intended for a page field in the Filters area and a single selected item. Check the field’s orientation, whether multiple selections are enabled, and whether the exact item exists after refreshing.

Can a macro filter several PivotTables at once?
Yes. Loop through the relevant PivotTables and apply the filter to each one. They may need compatible source caches or connections, and each report must contain the target field and item. Test each report because field captions and available items can differ.

Can I run PivotTable VBA in Excel for the web?
No. Excel for the web does not run VBA macros. Open the macro-enabled workbook in desktop Excel for Windows or Mac to use VBA; a web-based workflow needs a different automation option, such as Office Scripts where the required features are supported.

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.