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.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
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 inserted 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 that will 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.
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.
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!