How to Filter Pivot Tables Using VBA (With Examples)

---

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

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.