To filter a PivotTable by multiple criteria using VBA, you’ll want to interact with the `PivotFilters` or the `PivotFields` objects. Here’s a step-by-step guide to help you achieve this in Excel:
Example Situation
Assume you have a PivotTable with a field named “Category” and another field named “Date”. You want to filter this PivotTable to show only certain categories and a specific date range.
VBA Code Example
Here is an example VBA macro that filters a PivotTable based on multiple criteria:
Sub FilterPivotTable()
Dim ws As Worksheet
Dim pt As PivotTable
Dim pfCategory As PivotField
Dim pfDate As PivotField
Dim pi As PivotItem
' Specify the worksheet and the PivotTable
Set ws = ThisWorkbook.Worksheets("Sheet1")
Set pt = ws.PivotTables("PivotTable1")
' Specify the field for categories
Set pfCategory = pt.PivotFields("Category")
' Clear any existing filters
pfCategory.ClearAllFilters
' Filter the "Category" field to show only selected items
' Hide all items first
For Each pi In pfCategory.PivotItems
pi.Visible = False
Next pi
' Show only the desired items
pfCategory.PivotItems("Category1").Visible = True
pfCategory.PivotItems("Category2").Visible = True
' Specify the field for date
Set pfDate = pt.PivotFields("Date")
' Clear any existing filters
pfDate.ClearAllFilters
' Set a filter for the "Date" field
With pfDate
.PivotFilters.Add Type:=xlAfterOrEqualTo, Value1:=DateValue("2022-01-01")
.PivotFilters.Add Type:=xlBeforeOrEqualTo, Value1:=DateValue("2022-12-31")
End With
End Sub
Explanation
- Setup Worksheet and PivotTable References: Change `”Sheet1″` and `”PivotTable1″` to your actual worksheet and PivotTable names.
- Category Field Filter: It clears any existing filters and then sets a visibility filter to show only specific categories (“Category1” and “Category2”).
- Date Field Filter: Again, it clears any existing filters before adding date filters to include only dates between “2022-01-01” and “2022-12-31”.
Notes
- Make sure that the categories (“Category1” and “Category2”) and dates (adjust `”2022-01-01″` and `”2022-12-31″`) exist in your data.
- Adjust object names and conditions as needed to suit your specific requirements.
- Be cautious about large data sets as manipulating large PivotTables might be resource-intensive.
This basic setup can be adapted to different fields and filter types (such as numeric, etc.) by changing the field names and filter conditions.
Unlock Your Potential
Excel
Access
Power BI
