In this article, we will use Excel VBA to filter Pivot Table Based on a list. The list can be an array, range, or a dynamic named range.
Imagine a scenario where you have a massive Pivot Table in Excel filled with information, and you are on a quest to find specific data points that match a particular list. Manual filtering can be time-consuming, tedious, and prone to error. But fear not, for VBA is here to save the day!
Create a Pivot Table in Excel
First, we will Create a Pivot Table in Excel. This is an example of a Pivot Table.
Excel VBA to Filter Pivot Table Based on List: 3 Methods
In this article, we will show you 3 methods to apply VBA to filter Pivot Table based on a list. Here in the dataset, we have the sales data for multiple fruits of three companies. The dataset includes the names of the items and their sales amounts for the three companies.
We have created a Pivot Table from the dataset. We have selected the Company, Item, and Sales fields for display. In the Columns area, we have put the Company field. In the Rows area, we have put the Item field. The Values area includes the Sum of Sales.
1. Apply VBA to Filter Pivot Table Based on Array List
We can apply the following VBA code to filter Pivot Table based on a list. Here the list is an array.
- Open the VBA macro editor from your workbook to write VBA code in Excel.
- Copy and paste the following code into your VBA Editor Module and press the Run button or F5 key to run the code:
Sub PivotTable_Filter_List_1()
On Error GoTo Txt
'variable declaration
Dim ws As Worksheet
Dim myArr() As String
Dim myStr As String
Dim myPT As PivotTable
Dim myPF As PivotField
'set variables
Set ws = ActiveSheet
Set myPT = ws.PivotTables(1)
Set myPF = myPT.PivotFields("Item")
myStr = InputBox("Please insert the items in a comma separated way")
myArr = Split(myStr, ",")
'clear previous filters
myPF.ClearAllFilters
'filter Pivot Table based on an array
For i = 1 To myPF.PivotItems.Count
counter = 0
For j = LBound(myArr) To UBound(myArr)
If myArr(j) = myPF.PivotItems(i) Then
counter = counter + 1
End If
Next j
If counter = 0 Then
myPF.PivotItems(i).Visible = False
End If
Next i
Exit Sub
'error text
Txt:
MsgBox "Not Found"
End SubVBA Breakdown
Sub PivotTable_Filter_List_1()- This line defines the start of a subroutine called PivotTable_Filter_List_1.
On Error GoTo Txt- This line defines an error-handling text that is described later in the code.
Dim ws As Worksheet
Dim myArr() As String
Dim myStr As String
Dim myPT As PivotTable
Dim myPF As PivotField- It declares variables for the worksheet as ws, an array of strings as myArr, a string as myStr, a PivotTable as myPT, and a PivotField as myPF.
Set ws = ActiveSheet
Set myPT = ws.PivotTables(1)
Set myPF = myPT.PivotFields("Item")- It sets the worksheet variable (ws) to the active sheet, the PivotTable variable (myPT) to the first Pivot Table on the worksheet, and the PivotField variable (myPF) to the “Item” field of the Pivot Table.
myStr = InputBox("Please insert the items in a comma separated way")- It prompts the user to enter items separated by commas using an InputBox and assigns the input to the string variable (myStr).
myArr = Split(myStr, ",")- It splits the string of items into an array using the comma as the delimiter and assigns it to the array variable (myArr).
myPF.ClearAllFilters- It clears any previous filters applied to the PivotField.
For i = 1 To myPF.PivotItems.Count
counter = 0
For j = LBound(myArr) To UBound(myArr)
If myArr(j) = myPF.PivotItems(i) Then
counter = counter + 1
End If
Next j
If counter = 0 Then
myPF.PivotItems(i).Visible = False
End If
Next i- The nested For loop iterates through each PivotItem in the PivotField and checks if it matches any item in the array. If an item in the PivotField does not match any item in the array, it sets the visibility of that PivotItem to False. That is how it filters out the Pivot Table.
Exit Sub- It exits the subroutine.
Txt:
MsgBox "Not Found"
End Sub- The code includes error handling with the label “Txt” and displays a MsgBox if an error occurs during the execution of the code. Overall, this code filters a Pivot Table based on an array input.
- In the InputBox, put the names of the items in a comma-separated way and press OK.
- Finally, you will see the Pivot Table filtered with your list.
2. Use VBA to Filter Pivot Table Based on Range List
We can apply the following VBA code to filter Pivot Table based on a list. Here the list is a range.
- Paste the following code in your VBA Editor Module and press the Run button or F5 key to run the code:
Sub PivotTable_Filter_List_2()
On Error GoTo Txt
'variable declaration
Dim ws As Worksheet
Dim myRng As Range
Dim myPT As PivotTable
Dim myPF As PivotField
'set variables
Set ws = ActiveSheet
Set myRng = Application.InputBox("Please select the list from the worksheet", Type:=8)
Set myPT = ws.PivotTables(1)
Set myPF = myPT.PivotFields("Item")
'clear previous filters
myPF.ClearAllFilters
'filter Pivot Table based on a range
For i = 1 To myPF.PivotItems.Count
counter = 0
For j = 1 To myRng.Cells.Count
If myRng.Cells(j) = myPF.PivotItems(i) Then
counter = counter + 1
End If
Next j
If counter = 0 Then
myPF.PivotItems(i).Visible = False
End If
Next i
Exit Sub
'error text
Txt:
MsgBox "Not Found"
End SubVBA Breakdown
Sub PivotTable_Filter_List_2()- This line defines the start of a subroutine called PivotTable_Filter_List_2.
On Error GoTo Txt- This line defines an error-handling text that is described later in the code.
Dim ws As Worksheet
Dim myRng As Range
Dim myPT As PivotTable
Dim myPF As PivotField- It declares variables for the worksheet as ws, an range as myRng, a PivotTable as myPT, and a PivotField as myPF.
Set ws = ActiveSheet
Set myRng = Application.InputBox("Please select the list from the worksheet", Type:=8)
Set myPT = ws.PivotTables(1)
Set myPF = myPT.PivotFields("Item")
- The ws variable is set to the currently active worksheet. An InputBox appears, prompting the user to select a range of cells, which is then assigned to the myRng The value of myPT variable is set to the first Pivot Table. The myPF variable is set to the PivotField named “Item” within the Pivot Table.
myPF.ClearAllFilters- The previous filters on the PivotField are cleared using the ClearAllFilters method.
For i = 1 To myPF.PivotItems.Count
counter = 0
For j = 1 To myRng.Cells.Count
If myRng.Cells(j) = myPF.PivotItems(i) Then
counter = counter + 1
End If
Next j
If counter = 0 Then
myPF.PivotItems(i).Visible = False
End If
Next i- A loop is initiated to iterate through each PivotItem in the PivotField. Within the loop, another loop checks if each item in the selected range matches the current PivotItem. A counter variable keeps track of the number of matches found. If no matches are found (counter = 0), the corresponding PivotItem is set to be invisible (not visible in the Pivot Table).
Exit Sub- This command exits the sub.
Txt:
MsgBox "Not Found"
End Sub- The code includes error handling with the label “Txt” and displays a MsgBox if an error occurs during the execution of the code. Overall, this code filters a Pivot Table based on a range input.
- Select the desired range of items you want to filter in the InputBox from the worksheet and press OK.
- Finally, you will see the filtered Pivot Table based on your list.
3. Apply VBA to Filter Pivot Table Based on Dynamic Named Range List
We can apply the following VBA code to filter Pivot Table based on a list. Here the list is a dynamic named range.
- Create a dynamic named range. This article will teach you How to Create Dynamic Named Range in Excel.
- Select cell H5 of the Dynamic_Named_Range sheet and enter the following formula in the Name Manager:
=OFFSET(Dynamic_Named_Range!$H$5,,,COUNTA(Dynamic_Named_Range!$H$5:$H$20)Formula Breakdown
COUNTA(Dynamic_Named_Range!$H$5:$H$20)- This will count the cells that are non-blanks in the range H5:H20.
Result: 3
OFFSET(Dynamic_Named_Range!$H$5,,,COUNTA(Dynamic_Named_Range!$H$5:$H$20)- Returns the range of cells from the sheet named Dynamic_Named_Range. The number of columns included in the range is not offset from the starting point, resulting in a range with a variable number of rows but no column offset.
Result: {“Apple”;”Banana”;”Mango”}
- Paste the following code in your VBA Editor Module and press the Run button or F5 key to run the code:
Sub PivotTable_Filter_List_3()
On Error GoTo Txt
'variable declaration
Dim ws As Worksheet
Dim myRng As Range
Dim myPT As PivotTable
Dim myPF As PivotField
'set variables
Set ws = ActiveSheet
Set myPT = ws.PivotTables(1)
Set myPF = myPT.PivotFields("Item")
Set myRng = ws.Range("List")
'clear previous filters
myPF.ClearAllFilters
'filter Pivot Table based on dynamic named range
For i = 1 To myPF.PivotItems.Count
counter = 0
For j = 1 To myRng.Cells.Count
If myRng.Cells(j) = myPF.PivotItems(i) Then
counter = counter + 1
End If
Next j
If counter = 0 Then
myPF.PivotItems(i).Visible = False
End If
Next i
Exit Sub
'error text
Txt:
MsgBox "Not Found"
End SubVBA Breakdown
Sub PivotTable_Filter_List_3()- This code starts with the subroutine declaration: PivotTable_Filter_List_3.
On Error GoTo Txt- In the next line, error handling is set up. This means if there is an error during runtime, the program will jump to the Txt label for error handling.
Dim ws As Worksheet
Dim myRng As Range
Dim myPT As PivotTable
Dim myPF As PivotField- Four variables are declared. Here, ws is a Worksheet Object, myRng is a Range Object, myPT is a PivotTable Object, myPF is a PivotField Object.
Set ws = ActiveSheet
Set myPT = ws.PivotTables(1)
Set myPF = myPT.PivotFields("Item")
Set myRng = ws.Range("List")- We set the ws variable to the currently active worksheet, the value of myPT variable to the first Pivot Table and the myPF variable to the PivotField named “Item” within the Pivot Table. myRng variable is set to the dynamic named range (List) of the worksheet ws.
myPF.ClearAllFilters- The ClearAllFilters method is called on myPF to remove any previous filters applied to the PivotField.
For i = 1 To myPF.PivotItems.Count
counter = 0
For j = 1 To myRng.Cells.Count
If myRng.Cells(j) = myPF.PivotItems(i) Then
counter = counter + 1
End If
Next j
If counter = 0 Then
myPF.PivotItems(i).Visible = False
End If
Next i- We initiated a For loop to iterate through each PivotItem in the PivotField. Within the loop, another loop checks if each item in the selected range matches the current PivotItem. A counter variable keeps track of the number of matches found. If the loop finds no matches (counter = 0), it will set the corresponding PivotItem invisible (not visible in the Pivot Table).
Exit Sub- This line exits the subroutine.
Txt:
MsgBox "Not Found"
End Sub- This is an error-handling message. If an error occurs, a MsgBox appears saying “Not Found”.
- You will see the filtered Pivot Table based on the dynamic named range.
- We used a dynamic named range in the VBA code, so it will update the Pivot Table automatically. Omit Mango from the list.
- Run the code again, and you will see the Pivot Table filtered with the dynamic named range.
Read More: How to Use Excel VBA to Filter Pivot Table
Excel VBA to Create UserForm to Filter Pivot Table Based on List
We can create a UserForm to filter Pivot Table based on a list. We will use the following VBA code in the UserForm to filter the list.
Simply follow the steps below to create the UserForm:
- Go to Insert >> UserForm.
- From the Toolbox, you can choose different controls for your UserForm.
- We have inserted a Label to write instructions and RefEdit to take a range as user input. We have also inserted a CommandButton to see the result.
- Double-click on the CommandButton. A new window will open. Enter the following code:
Private Sub CommandButton1_Click()
On Error GoTo Txt
'variable declaration
Dim ws As Worksheet
Dim myRng As Range
Dim myPT As PivotTable
Dim myPF As PivotField
'set variables
Set myRng = Range(RefEdit1.Value)
Set ws = ActiveSheet
Set myPT = ws.PivotTables(1)
Set myPF = myPT.PivotFields("Item")
'clear previous filters
myPF.ClearAllFilters
'filter Pivot Table based on list
For i = 1 To myPF.PivotItems.Count
counter = 0
For j = 1 To myRng.Cells.Count
If myRng.Cells(j) = myPF.PivotItems(i) Then
counter = counter + 1
End If
Next j
If counter = 0 Then
myPF.PivotItems(i).Visible = False
End If
Next i
Exit Sub
'error text
Txt:
MsgBox "Not Found"
End SubVBA Breakdown
Private Sub CommandButton1_Click()- This line defines the start of an event called CommanButtin1_Click.
On Error GoTo Txt- This line defines an error-handling text that we will describe later in the code.
Dim ws As Worksheet
Dim myRng As Range
Dim myPT As PivotTable
Dim myPF As PivotField- We declared four variables. Here, ws is a Worksheet Object, myRng is a Range Object, myPT is a PivotTable Object, myPF is a PivotField Object.
Set myRng = Range(RefEdit1.Value)- This line assigns the range specified by the value of the RefEdit1 control (a range input) to the variable myRng.
Set ws = ActiveSheet
Set myPT = ws.PivotTables(1)
Set myPF = myPT.PivotFields("Item")- It sets the worksheet variable (ws) to the active sheet, the PivotTable variable (myPT) to the first Pivot Table on the worksheet, and the PivotField variable (myPF) to the “Item” field of the Pivot Table.
myPF.ClearAllFilters- This line will clear the previous filters on the PivotField using the ClearAllFilters method.
For i = 1 To myPF.PivotItems.Count
counter = 0
For j = 1 To myRng.Cells.Count
If myRng.Cells(j) = myPF.PivotItems(i) Then
counter = counter + 1
End If
Next j
If counter = 0 Then
myPF.PivotItems(i).Visible = False
End If
Next i- This section of code iterates through each PivotItem in the PivotField. Within the loop, another loop checks if each item in the myRng range matches the current PivotItem. A counter variable keeps track of the number of matches found. If it finds no matches (counter = 0), it will set the corresponding PivotItem to invisible (not visible in the Pivot Table).
Exit Sub- This line exits the subroutine.
Txt:
MsgBox "Not Found"
End Sub- The code includes error handling with the label “Txt” and displays a message “Not Found” in a MsgBox if an error occurs during the execution of the code.
- Run the UserForm.
- Select the appropriate range that you will use as a list to filter the Pivot Table.
- Press the SUBMIT button.
- You will see the Pivot Table filtered based on the list you provided.
Things to Remember
There are a few things to remember while using VBA to filter Pivot Table based on the list:
- Set the correct data range or array as a list.
- Clear existing filters before applying new filters.
- Refresh the Pivot Table before applying the VBA code if there is any change in the dataset.
Frequently Asked Questions
1. What if the items in my filter list don’t exactly match the PivotTable field values?
If the items in your filter list don’t exactly match the Pivot Table field values, you may encounter errors or unexpected results. Make sure to handle such scenarios by including error handling statements such as On Error GoTo statement.
2. How can I filter multiple fields simultaneously?
To filter multiple fields simultaneously, you can apply filters to multiple Pivot Fields within your loop. Each field will have its own filter criteria, and you can set them accordingly based on your requirements.
3. How do I clear existing filters before applying new ones?
You can clear existing filters in a Pivot Table by using .ClearAllFilters method of the PivotField object. This ensures that you start with a clean filter state before applying new filters.
Download Practice Workbook
You can download this practice workbook while going through the article.
Conclusion
In this article, we have discussed 3 easy methods in detail to apply VBA to filter Pivot Table based on a list. This article will allow users to use Excel more efficiently and effectively. If you have any questions regarding this essay, feel free to let us know in the comments.
Related Articles
- Excel VBA to Filter Pivot Table Based on Multiple Cell Values
- Excel VBA Pivot Table to Filter Between Two Dates
- Excel VBA to Filter Pivot Table Based on Cell Value
<< Go Back to Pivot Table Filter | Pivot Table in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
