Excel VBA to Filter Pivot Table Based on Cell Value (3 Examples)

In this article, I am going to show you how we can use VBA code to filter Pivot Table based on cell value in Excel. As we know, Pivot Table is a great tool to summarize large amounts of data automatically. Pivot Table also offers filtering data.

However, if we need to change the filtering in the Pivot Table based on cell value frequently, we may want to automate the filtering process using VBA. Automating filtering gives us the advantage of saving a large amount of time by preventing us from doing the same task repeatedly.

How to Use Excel VBA to Filter Pivot Table Based on Cell Value: 3 Examples

In this section, I will give 3 examples of using VBA to filter Pivot Table based on cell value in Excel. To illustrate, I have taken a dataset containing information on the customer care unit of a company. Below, we have created a Pivot Table based on data.

Now, let’s explore the examples one by one.

1. Create a Filter in PivotTable Based on Cell Value

In the first example, we will use a VBA code to Filter the Pivot Table based on a cell value. For instance, I want to filter the previously shown Pivot Table based on High Priority. So, I write the filter words in a cell, F26.

One thing to note is that, as we want to filter the PivotTable according to the Priority field, before running the code, we should drag the Priority field to the Filters orientation.

To filter the Pivot Table based on the cell value in F26, we can run the following 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

After running the code, our PivotTable will be filtered by the cell value of F26 which is High.

2. Create a Filter in Pivot Table Using Multiple Cell Values

In this example, we will run a VBA code to filter a Pivot Table based on multiple cell values. For illustration, I have written my two filter keywords in the range F26:G26.

Our target is to filter the Pivot Table based on these multiple cell values. To do that, I have run the following 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

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.

After running the code, we get the desired multi-filtered Pivot Table.

3. Creating a Row Filter in PivotTable Based on Cell Value

Using VBA, we can also filter rows of any PivotTable based on a cell value and only allow to display the rows that we want. For example, I have re-oriented the previously used Pivot Table like this.

Now, we want to filter the rows based on Technical Category so that only the row containing Technical category will be displayed. Hence, we put the word Technical inside cell G26. Now, to filter the rows of the Pivot Table based on the cell value, I run the following 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

After running the code, the Pivot Table was filtered according to the cell value of G26.

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

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.

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

After running the code, we will get the same result as we saw in the 1st example.

How to Clear All Filters Using Excel VBA

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.
After running the code, it removed all the 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, you must drag that PivotField into the Filters orientation before running the provided VBA code. Otherwise, you will get an Error.
In the provided codes, you must change the worksheet name, PivotTable name, PivotField name, and cell reference according to your setup.

Frequently Asked Questions

1. How do you filter a pivot table by cell value in VBA?

You can use the 1st example of this article to filter a Pivot Table by cell value.

2. Can I use a cell reference to filter a pivot table?

Yes, absolutely! Check our first example of this article.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Filtering Pivot Table Based on Cell Value.xlsm

Conclusion

In this article, I gave 3 different examples of using VBA to filter the Pivot Table based on cell value to develop a complete understanding of this topic. I hope you enjoyed this article. If you find this article helpful, please share it with your friends and family. Moreover, do let us know in the comment box if you have any further queries. Have a nice day, goodbye!

<< Go Back to Pivot Table Filter | Pivot Table in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!