How to apply a filter to a PivotTable using VBA?

Applying a filter to a PivotTable using VBA involves a few key steps, including specifying the PivotTable, the field you want to filter, and the criteria for the filter. Here’s a basic example of how you can apply a filter to a PivotTable using VBA:

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

    ' Set the worksheet where the PivotTable is located
    Set ws = ThisWorkbook.Sheets("Sheet1")  ' Adjust the sheet name as necessary

    ' Set the PivotTable you want to work with
    Set pt = ws.PivotTables("PivotTable1")  ' Adjust the PivotTable name as necessary

    ' Set the PivotField you want to apply the filter to
    Set pf = pt.PivotFields("Field_Name")  ' Adjust the field name as necessary

    ' Clear previous filters
    pf.ClearAllFilters

    ' Apply a filter to include only a specific item
    ' Replace "Item_Name" with the actual item you want to filter by
    pf.CurrentPage = "Item_Name"

    ' Alternatively, to apply a filter to exclude specific items, use the following:
    ' pf.HiddenItemsList = Array("Unwanted_Item_Name")

    ' If you want to apply multiple filters, you need to use the PivotItems method
    ' Example: Hide all items and then show only the selected ones
    ' Dim pi As PivotItem
    ' For Each pi In pf.PivotItems
    '     pi.Visible = False
    ' Next pi
    ' pf.PivotItems("Item_Name").Visible = True

End Sub

Key Points:

ws and pt Variables: These are used to set references to your worksheet and PivotTable, respectively. You need to update these with your specific sheet and PivotTable names.
pf Variable: This refers to the PivotField you want to filter. Replace `”Field_Name”` with the name of the field you’re interested in.
CurrentPage: This is used when dealing with PageFields (that act as filters) to set the filter criterion to a specific item.
ClearAllFilters: This method clears any existing filters on the specified PivotField before applying new criteria. This is useful to start fresh with your filtering logic.
HiddenItemsList: An alternative method to hide specific items from the PivotField by specifying an array of item names you want to exclude.
Visible Property: When dealing with PivotItems, this property allows you to control the visibility of each item individually, offering fine-grain control over what is displayed in the PivotTable.

Adjust the parameters and logic as necessary to fit your specific needs and PivotTable structure.

facebooklinkedin