You can use the following methods to filter pivot tables in Excel using VBA:
Method 1: Filter Pivot Table Based on One Value
Sub FilterPivotTable()
Dim pf As PivotField
Dim myFilter As String
Set pf = ActiveSheet.PivotTables("PivotTable1").PivotFields("Position")
myFilter = ActiveWorkbook.Sheets("Sheet1").Range("J2").Value
pf.PivotFilters.Add2 xlCaptionEquals, , myFilter
End Sub
This particular macro will filter the pivot table called PivotTable1 to only display rows where the value in the Position column of the pivot table is equal to the value in cell J2 of Sheet1.
Method 2: Filter Pivot Table Based on Multiple Values
Sub FilterPivotTableMultiple()
Dim v As Variant
Dim i As Integer, j As Integer
Dim pf As PivotField
Set pf = ActiveSheet.PivotTables("PivotTable1").PivotFields("Position")
'specify range with values to filter on
v = Range("J2:J3")
'clear existing filters
pf.ClearAllFilters
'apply filter to pivot table
With pf
For i = 1 To pf.PivotItems.Count
j = 1
Do While j <= UBound(v, 1) - LBound(v, 1) + 1
If pf.PivotItems(i).Name = v(j, 1) Then
pf.PivotItems(pf.PivotItems(i).Name).Visible = True
Exit Do
Else
pf.PivotItems(pf.PivotItems(i).Name).Visible = False
End If
j = j + 1
Loop
Next i
End With
End Sub
This particular macro will filter the pivot table called PivotTable1 to only display rows where the value in the Position column of the pivot table is equal to one of the values in the cell range J2:J3.
Method 3: Clear Filters from Pivot Table
Sub ClearPivotTableFilter()
Dim pt As PivotTable
Set pt = ActiveSheet.PivotTables("PivotTable1")
pt.ClearAllFilters
End Sub
This particular macro will clear all filters from the pivot table called PivotTable1.
The following examples show how to use each of these methods in practice.
Example 1: Filter Pivot Table Based on One Value
Suppose we have created a pivot table from a dataset in Excel to summarize the points scored by basketball players on various teams and positions:
Suppose we would like to filter the pivot table to only show the rows where the value in the Position column is Guard.
We can create the following macro to do so:
Sub FilterPivotTable()
Dim pf As PivotField
Dim myFilter As String
Set pf = ActiveSheet.PivotTables("PivotTable1").PivotFields("Position")
myFilter = ActiveWorkbook.Sheets("Sheet1").Range("J2").Value
pf.PivotFilters.Add2 xlCaptionEquals, , myFilter
End Sub
When we run this macro, the pivot table is automatically filtered to only show rows where the value in the Position column is Guard:
The pivot table has been filtered to only show rows where the value in the Position column is Guard.
Example 2: Filter Pivot Table Based on Multiple Values
Suppose we would instead like to filter the pivot table to only show rows where the value in the Position column is Guard or Center.
We can create the following macro to do so:
Sub FilterPivotTableMultiple()
Dim v As Variant
Dim i As Integer, j As Integer
Dim pf As PivotField
Set pf = ActiveSheet.PivotTables("PivotTable1").PivotFields("Position")
'specify range with values to filter on
v = Range("J2:J3")
'clear existing filters
pf.ClearAllFilters
'apply filter to pivot table
With pf
For i = 1 To pf.PivotItems.Count
j = 1
Do While j <= UBound(v, 1) - LBound(v, 1) + 1
If pf.PivotItems(i).Name = v(j, 1) Then
pf.PivotItems(pf.PivotItems(i).Name).Visible = True
Exit Do
Else
pf.PivotItems(pf.PivotItems(i).Name).Visible = False
End If
j = j + 1
Loop
Next i
End With
End Sub
When we run this macro, the pivot table is automatically filtered to only show rows where the value in the Position column is Guard or Center:
The pivot table has been filtered to only show rows where the value in the Position column is Guard or Center.
Additional Resources
The following tutorials explain how to perform other common tasks in VBA:
VBA: How to Refresh Pivot Tables
VBA: How to Remove Duplicate Values
VBA: How to Count Number of Rows in Range