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

Method 1 – Create a Filter in a PivotTable Based on Cell Value:

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).

Writing Filter Keyword in a Cell

Preparation:

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

Dragging Desired Pivot Field in the Filters Orientation

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

VBA Code:
VBA Code for Creating a Filter in PivotTable Based on Cell Value

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).

Filtered PivotTable Based on Cell Value


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:

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

Dataset for Creating a Filter in PivotTable Using Multiple Cell Values

VBA Code:

VBA Code to filter pivot table based on cell Multiple values

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

How Does the Code Work?

Fltr_KW_array = Range("F26:G26")
  • Here, we are storing the values in the range of Filter Keywords to an array named Fltr_KW_array.
      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
  • In this part, there are two loops. In the first loop, the inner codes will be iterated for i=1 to the total number of items in the filtered Pv_Field. Then, in the 2nd loop, it is checked whether the PivotItems match any of the Filtered Keywords. If it matches, then its visibility is set to True, else, it is set to False. This loop continues until all the Filtered Keywords are checked.

Execution:

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

Result After Running VBA Code to Filter Pivot Table Based on Multiple Cell Values


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

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

Dataset for Creating a Row Filter in PivotTable Based on Cell Value

Objective:

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

Preparation:

  • Write the word Technical inside cell G26.

VBA Code:

VBA Code for Creating a Row Filter in PivotTable Based on Cell Value

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.

Result After Running VBA Code for Creating a Row Filter in PivotTable Based on Cell Value


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:

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

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.

Result After Running VBA Code to Create a Filter Based on a Variable Using Excel VBA


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.

VBA Code for Clearing All Filters in a Pivot Table

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.

Result After Running VBA Code for Clearing All Filters in a 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


Get FREE Advanced Excel Exercises with Solutions!
Aniruddah Alam
Aniruddah Alam

Md. Aniruddah Alam, a graduate of Bangladesh University of Engineering & Technology, Dhaka, holds a BSc.Engg in Naval Architecture & Marine Engineering. Joining Softeko in August 2022 as an Excel and VBA content developer, he has authored 90+ articles, covering fundamental to advanced Excel topics. He actively develops VBA codes for Excel automation and offers solutions in the Exceldemy Forum. Beyond work, he finds leisure in reading books, and taking relaxing walks, showcasing well-rounded blend of professional and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo