How to Use Excel VBA to Filter a Pivot Table Based on Cell Value (3 Methods)

Method 1 – Create a Filter in a PivotTable

Scenario: We have a dataset containing information about the customer care unit of a company, and we’ve created a Pivot Table based on this data.

Objective: Filter the Pivot Table based on a specific cell value (e.g., High Priority).

Preparation:

• Ensure that the PivotTable field you want to filter (in this case, the Priority field) is already added to the Filters orientation.

• Enter the filter value (e.g., High Priority) in cell F26.

VBA Code:

Sub FilterPivot_SingleCell()
    Dim PV_WS As Worksheet
    Dim Pv_Table As PivotTable
    Dim Pv_Field As PivotField
    Dim Fltr_KW As String
    'Assigning Worksheet of Pivot Table
    Set PV_WS = Worksheets("Sheet1")
    'Assigning PivotTable Name
    Set Pv_Table = PV_WS.PivotTables("PivotTable3")
    'Assigning PivotTable Field to Filter
    Set Pv_Field = Pv_Table.PivotFields("Priority")
    'Assigning Cell Value to the Filter KeyWord
    Fltr_KW = Range("F26").Value
    'Clearing all filters
    Pv_Field.ClearAllFilters
    'Setting Filters to the cell value
    Pv_Field.CurrentPage = Fltr_KW
End Sub

Execution:

• Run the code. The PivotTable will be filtered based on the cell value in F26 (which is High).

Method 2 – Create a Filter in a Pivot Table Using Multiple Cell Values

Scenario: We want to filter the Pivot Table based on multiple cell values (e.g., High Priority and Medium Priority).

Preparation:

• Enter the filter keywords in the range F26:G26 (e.g., High Priority in F26 and Medium Priority in G26).

VBA Code:

Sub FilterPivot_MultipleCell()
    Dim PV_WS As Worksheet
    Dim Pv_Table As PivotTable
    Dim Pv_Field As PivotField
    Dim Fltr_KW_array As Variant
    Dim i As Integer, j As Integer
    'Assigning Worksheet of Pivot Table
    Set PV_WS = Worksheets("Sheet2")
    'Assigning PivotTable Name
    Set Pv_Table = PV_WS.PivotTables("PivotTable3")
    'Assigning PivotTable Field to Filter
    Set Pv_Field = Pv_Table.PivotFields("Priority")
    'Assigning Cell Value to the Filter KeyWord
    Fltr_KW_array = PV_WS.Range("F26:G26")
    'Clearing all filters
    Pv_Field.ClearAllFilters
      With Pv_Field
        For i = 1 To Pv_Field.PivotItems.Count
          j = 1
           Do While j <= UBound(Fltr_KW_array, 2) - LBound(Fltr_KW_array, 2) + 1
             If Pv_Field.PivotItems(i).Name = Fltr_KW_array(1, j) Then
               Pv_Field.PivotItems(Pv_Field.PivotItems(i).Name).Visible = True
               Exit Do
            Else
              Pv_Field.PivotItems(Pv_Field.PivotItems(i).Name).Visible = False
            End If
            j = j + 1
          Loop
        Next i
      End With
    End Sub

Fltr_KW_array = Range("F26:G26")

      With Pv_Field
        For i = 1 To Pv_Field.PivotItems.Count
          j = 1
           Do While j <= UBound(Fltr_KW_array, 2) - LBound(Fltr_KW_array, 2) + 1
             If Pv_Field.PivotItems(i).Name = Fltr_KW_array(1, j) Then
               Pv_Field.PivotItems(Pv_Field.PivotItems(i).Name).Visible = True
               Exit Do
            Else
              Pv_Field.PivotItems(Pv_Field.PivotItems(i).Name).Visible = False
            End If
            j = j + 1
          Loop
        Next i
      End With

Execution:

• Run the code. The PivotTable will be filtered based on the cell values in the range F26:G26.

Method 3 – Creating a Row Filter in a PivotTable Based on Cell Value

Scenario: We have re-oriented the previously used Pivot Table.

Objective:

• Filter the rows based on the Technical Category so that only the row containing the Technical category will be displayed.

Preparation:

• Enter the word Technical inside cell G26.

VBA Code:

Sub RowFilter_CellValue()
    Dim PV_WS As Worksheet
    Dim Pv_Table As PivotTable
    Dim Pv_Field As PivotField
    Dim Fltr_KW As String
    'Assigning Worksheet of Pivot Table
    Set PV_WS = Worksheets("Sheet3")
    'Assigning PivotTable Name
    Set Pv_Table = PV_WS.PivotTables("PivotTable3")
    'Assigning PivotTable Field to Filter
    Set Pv_Field = Pv_Table.PivotFields("Category")
    'Assigning Cell Value to the Filter KeyWord
    Fltr_KW = Range("G26").Value
    'Clearing all filters
    Pv_Field.ClearAllFilters
    'Filter Row Based on Cell Value
    Pv_Field.PivotFilters.Add2 xlCaptionEquals, , Fltr_KW
End Sub

Execution:

• Run the code.
• The Pivot Table will be filtered according to the cell value in G26.

How to Create a Filter Based on a Variable Using Excel VBA

Scenario:

• We want to filter the previously used PivotTable by High Priority.
• Instead of referring to any cell in the worksheet, we can directly hardcode the Fltr_KW filter PivotTable in Excel.
• For example, if I want to filter the previously used PivotTable by High Priority, we can directly assign the value High to the Fltr_KW variable.

VBA Code:

Sub FilterPivot_Variable()
    Dim PV_WS As Worksheet
    Dim Pv_Table As PivotTable
    Dim Pv_Field As PivotField
    Dim Fltr_KW As String
    'Assigning Worksheet of Pivot Table
    Set PV_WS = Worksheets("Sheet4")
    'Assigning PivotTable Name
    Set Pv_Table = PV_WS.PivotTables("PivotTable3")
    'Assigning PivotTable Field to Filter
    Set Pv_Field = Pv_Table.PivotFields("Priority")
    'Assigning the Filter KeyWord Directly
    Fltr_KW = "High"
    'Clearing all filters
    Pv_Field.ClearAllFilters
    'Setting Filters to the cell value
    Pv_Field.CurrentPage = Fltr_KW
End Sub

Execution:

• Run the code.
• The result will be the same as in the first example.

How to Clear All Filters Using Excel VBA

Scenario: We want to remove all existing filters in a Pivot Table.

VBA Code:

• To clear all the existing filters in a Pivot Table, we can use the Pv_Field.ClearAllFilters method, where Pv_Field is any field of a PivotTable.

Sub ClearAllFilter()
    Dim PV_WS As Worksheet
    Dim Pv_Table As PivotTable
    Dim Pv_Field As PivotField
    Dim Fltr_KW As String
    'Assigning Worksheet of Pivot Table
    Set PV_WS = Worksheets("Sheet4")
    'Assigning PivotTable Name
    Set Pv_Table = PV_WS.PivotTables("PivotTable3")
    'Clearing All Filters
    For Each Pv_Field In Pv_Table.PivotFields
        Pv_Field.ClearAllFilters
    Next
End Sub

• In the code, I have used a For Each Loop to loop through each PivotField and clear all filters in that PivotField.

Execution:

• Run the code. It will remove all filters from the Pivot Table.

Things to Remember

• If you want to create a filter on a specific PivotField using VBA based on cell value, ensure that you drag that PivotField into the Filters orientation before running the provided VBA code to avoid errors.
• In the provided codes, adjust the worksheet name, PivotTable name, PivotField name, and cell reference according to your specific setup.

Download Practice Workbook

You can download the practice workbook from here:

Related Articles

• Excel VBA Pivot Table to Filter Between Two Dates
• Excel VBA to Filter Pivot Table Based on List