Excel Pivot Tables: Filter Data, Items, Values & Dates using VBA
Related Links:
1. Create an Excel Pivot Table report using VBA
2. Referencing an Excel Pivot Table Range using VBA
3. Reference Pivot Fields and Pivot Items in an Excel Pivot Table, using VBA
4. Excel Pivot Table Address, Location, Move & Copy using VBA
5. Excel Pivot Table Layout and Design, using VBA
6. Excel Pivot Table Properties & Settings, using VBA
7. Refresh Excel Pivot Table and Cache of PivotTable, using VBA
8. Excel Pivot Tables Grouping: Group Items, Group Data and Group Date Values, using VBA
9. Excel Pivot Tables: Sort Fields, Values & Dates, use Custom Lists, with VBA
10. Excel Pivot Tables: Filter Data, Items, Values & Dates using VBA
11. Excel Pivot Tables: Summary Functions, Custom Calculations & Value Field Settings, using VBA
12. Excel Pivot Tables: Insert Calculated Fields & Calculated Items, Create Formulas using VBA
13. Create & Customize Excel Pivot Table Charts, using VBA
Refer complete Tutorial on using Pivot Tables in Excel Window (user interface):
Create and Customize a Pivot Table report
————————————————————————————————————–
Contents:
1. Use the PivotField.AutoShow method to display the Top or Bottom items in a PivotTable Field
2. PivotFilters.Add Method – use this to add new filters to a PivotTable report
5. PivotTable.ActiveFilters Property
6. PivotTable.AllowMultipleFilters Property
————————————————————————————————————–
In Excel 2007, multiple filters can be applied to a PivotField: Manual filter, Label Filter, Value filter and Date filter.
You can hide or display data to show in a PivotTable report by using Filters. This way you can focus not only on a specific field or item but also the determine the criteria of displayed values viz. suppose you want to display sales of only a particular car model(s) and just the top 10 sales figures, you can use filters to narrow down the report by selecting the specified model(s) and choosing ‘Top 10’ in the value filters.
1. Use the PivotField.AutoShow method to display the Top or Bottom items in a PivotTable Field
Use the PivotField.AutoShow method to display the Top or Bottom items for a Row, Column or Page (Report Filter) field in a PivotTable report. All 4 arguments of Type, Range, Count and Field in this method are mandatory to specify. For Type you have 2 options – xlAutomatic displays the items that match the specified criteria while xlManual disables this; Range can be xlTop or xlBottom to show Top or Bottom items; Count specifies the number of Top or Bottom items to be displayed; Field refers to the base data field name.
Use this method for filtering Pivot Table version number 2 (xlPivotTableVersion11) or earlier.
Example 1: Display the top 3 cities, based on Sum of Budgeted Sales. Refer Image 1 – the PivotTable at the top is before applying the filter and the PivotTable at the bottom is after applying the filter with the following code.
Sub PivotTableFilter1()
Dim PvtTbl As PivotTable
Set PvtTbl = Worksheets(“Sheet1”).PivotTables(“PivotTable1”)
‘PivotTable.Version Property – returns the PivotTable version number, 1, 2, 3, etc.
MsgBox PvtTbl.Version
PvtTbl.PivotFields(“City”).AutoShow Type:=xlAutomatic, Range:=xlTop, Count:=3, Field:=“Sum of Budgeted Sales”
End Sub
Example 2: Display the bottom 3 cities, based on Sum of Sales. Refer Image 2 – the PivotTable at the top is before applying the filter and the PivotTable at the bottom is after applying the filter with the following code.
Sub PivotTableFilter2()
Dim PvtTbl As PivotTable
Set PvtTbl = Worksheets(“Sheet1”).PivotTables(“PivotTable1”)
‘PivotTable.Version Property – returns the PivotTable version number, 1, 2, 3, etc.
MsgBox PvtTbl.Version
PvtTbl.PivotFields(“City”).AutoShow Type:=xlAutomatic, Range:=xlBottom, Count:=3, Field:=“Sum of Sales”
End Sub
2. PivotFilters.Add Method – use this to add new filters to a PivotTable report.
The Type argument in this method is necessary to mention, and can be of the following filter types as given in XlPivotFilterType Enumeration:
xlBefore, xlBeforeOrEqualTo, xlAfter, xlAfterOrEqualTo, xlAllDatesInPeriodJanuary, xlAllDatesInPeriodFebruary, xlAllDatesInPeriodMarch , xlAllDatesInPeriodApril, xlAllDatesInPeriodMay, xlAllDatesInPeriodJune , xlAllDatesInPeriodJuly, xlAllDatesInPeriodAugust, xlAllDatesInPeriodSeptember, xlAllDatesInPeriodOctober, xlAllDatesInPeriodNovember, xlAllDatesInPeriodDecember, xlAllDatesInPeriodQuarter1, xlAllDatesInPeriodQuarter2, xlAllDatesInPeriodQuarter3, xlAllDatesInPeriodQuarter4, xlBottomCount, xlBottomPercent, xlBottomSum, xlCaptionBeginsWith, xlCaptionContains, xlCaptionDoesNotBeginWith, xlCaptionDoesNotContain, xlCaptionDoesNotEndWith, xlCaptionDoesNotEqual, xlCaptionEndsWith, xlCaptionEquals, xlCaptionIsBetween, xlCaptionIsGreaterThan, xlCaptionIsGreaterThanOrEqualTo, xlCaptionIsLessThan, xlCaptionIsLessThanOrEqualTo, xlCaptionIsNotBetween, xlDateBetween, xlDateLastMonth, xlDateLastQuarter, xlDateLastWeek, xlDateLastYear, xlDateNextMonth, xlDateNextQuarter, xlDateNextWeek, xlDateNextYear, xlDateThisMonth, xlDateThisQuarter, xlDateThisWeek, xlDateThisYear, xlDateToday, xlDateTomorrow, xlDateYesterday, xlNotSpecificDate, xlSpecificDate, xlTopCount, xlTopPercent, xlTopSum, xlValueDoesNotEqual, xlValueEquals, xlValueIsBetween, xlValueIsGreaterThan, xlValueIsGreaterThanOrEqualTo, xlValueIsLessThan, xlValueIsLessThanOrEqualTo, xlValueIsNotBetween, xlYearToDate.
Example 3: Filter all dates in the month of April – refer Image 3 – the PivotTable on the left is before applying the filter and the PivotTable on the right is after applying the filter.
Sub PivotTableFilter3()
Dim PvtTbl As PivotTable
Set PvtTbl = Worksheets(“Sheet12”).PivotTables(“PivotTable1”)
‘delete all filters currently applied to the PivotTable, using the PivotTable.ClearAllFilters Method
PvtTbl.ClearAllFilters
PvtTbl.PivotFields(“Dates”).PivotFilters.Add Type:=xlAllDatesInPeriodApril
End Sub
Example 4: Filter all dates between a range of dates – refer Image 4 – the PivotTable on the left is before applying the filter and the PivotTable on the right is after applying the filter.
Sub PivotTableFilter4()
Dim PvtTbl As PivotTable
Set PvtTbl = Worksheets(“Sheet12”).PivotTables(“PivotTable1”)
‘delete all filters currently applied to the PivotTable, using the PivotTable.ClearAllFilters Method
PvtTbl.ClearAllFilters
PvtTbl.PivotFields(“Dates”).PivotFilters.Add Type:=xlDateBetween, Value1:=“4/28/2009”, Value2:=“5/20/2009”
End Sub
Example 5: Filter all captions matching a specified string – refer Image 5 – the PivotTable on the left is before applying the filter and the PivotTable on the right is after applying the filter.
Sub PivotTableFilter5()
Dim PvtTbl As PivotTable
Set PvtTbl = Worksheets(“Sheet1”).PivotTables(“PivotTable1”)
‘delete all filters currently applied to the PivotTable, using the PivotTable.ClearAllFilters Method
PvtTbl.ClearAllFilters
PvtTbl.PivotFields(“City”).PivotFilters.Add Type:=xlCaptionContains, Value1:=“LO”
End Sub
Example 6: Filter all captions greater than a specified value – refer Image 6 – the PivotTable on the left is before applying the filter and the PivotTable on the right is after applying the filter.
Sub PivotTableFilter6()
Dim PvtTbl As PivotTable
Set PvtTbl = Worksheets(“Sheet1”).PivotTables(“PivotTable1”)
‘delete all filters currently applied to the PivotTable, using the PivotTable.ClearAllFilters Method
PvtTbl.ClearAllFilters
PvtTbl.PivotFields(“City”).PivotFilters.Add Type:=xlCaptionIsGreaterThan, Value1:=“N”
End Sub
Example 7: Filter all captions between a specified range of values – refer Image 7 – the PivotTable on the left is before applying the filter and the PivotTable on the right is after applying the filter.
Sub PivotTableFilter7()
Dim PvtTbl As PivotTable
Set PvtTbl = Worksheets(“Sheet1”).PivotTables(“PivotTable1”)
‘delete all filters currently applied to the PivotTable, using the PivotTable.ClearAllFilters Method
PvtTbl.ClearAllFilters
PvtTbl.PivotFields(“City”).PivotFilters.Add Type:=xlCaptionIsBetween, Value1:=“M”, Value2:=“U”
End Sub
Example 8: Filter all values less than or equal to a specified value – refer Image 8 – the PivotTable on the left is before applying the filter and the PivotTable on the right is after applying the filter.
Sub PivotTableFilter8()
Dim PvtTbl As PivotTable
Set PvtTbl = Worksheets(“Sheet1”).PivotTables(“PivotTable1”)
‘delete all filters currently applied to the PivotTable, using the PivotTable.ClearAllFilters Method
PvtTbl.ClearAllFilters
PvtTbl.PivotFields(“City”).PivotFilters.Add Type:=xlValueIsLessThanOrEqualTo, DataField:=PvtTbl.PivotFields(“Sum of Sales”), Value1:=4095
End Sub
Example 9: Filter the top specified number of values based on a Data Field – refer Image 9 – the PivotTable on the left is before applying the filter and the PivotTable on the right is after applying the filter.
Sub PivotTableFilter9()
Dim PvtTbl As PivotTable
Set PvtTbl = Worksheets(“Sheet1”).PivotTables(“PivotTable1”)
‘delete all filters currently applied to the PivotTable, using the PivotTable.ClearAllFilters Method
PvtTbl.ClearAllFilters
PvtTbl.PivotFields(“City”).PivotFilters.Add Type:=xlTopCount, DataField:=PvtTbl.PivotFields(“Sum of Sales”), Value1:=3
End Sub
Example 10: Show or hide an item in a PivotTable Field – True setting will show and False will hide:
Sub PivotTableFilter10()
Dim PvtTbl As PivotTable
Set PvtTbl = Worksheets(“Sheet1”).PivotTables(“PivotTable1”)
PvtTbl.PivotFields(“Region”).PivotItems(“Europe”).Visible = False
End Sub
Example 11: Show all items in a Pivot Field.
Sub PivotTableFilter11()
Dim PvtTbl As PivotTable
Dim pvtItm As PivotItem
Set PvtTbl = Worksheets(“Sheet1”).PivotTables(“PivotTable1”)
For Each pvtItm In PvtTbl.PivotFields(“Region”).PivotItems
pvtItm.Visible = True
Next
End Sub
Example 12: Loop through all items in a PivotTable Field to hide or show them.
Sub PivotTableFilter12()
Dim PvtTbl As PivotTable
Dim pvtItm As PivotItem
Set PvtTbl = Worksheets(“Sheet1”).PivotTables(“PivotTable1”)
For Each pvtItm In PvtTbl.PivotFields(“Region”).PivotItems
If MsgBox(“Hide Item ” & pvtItm & “?”, vbYesNo) = vbYes Then
pvtItm.Visible = False
End If
Next
End Sub
Example 13: Return all items in a PivotTable Field which are visible – refer Image 10 – on the left side is the filtered PivotTable and on the right side is the message box displaying only the visible items.
Sub PivotTableFilter13()
Dim PvtTbl As PivotTable
Dim pvtItm As PivotItem
Dim visPvtItm As String
Set PvtTbl = Worksheets(“Sheet1”).PivotTables(“PivotTable1”)
PvtTbl.PivotFields(“City”).PivotItems(“London”).Visible = False
PvtTbl.PivotFields(“City”).PivotItems(“Nice”).Visible = False
PvtTbl.PivotFields(“City”).PivotItems(“Vancouver”).Visible = False
For Each pvtItm In PvtTbl.PivotFields(“City”).PivotItems
If pvtItm.Visible = True Then
visPvtItm = visPvtItm & “, “ & pvtItm
End If
Next
MsgBox Mid(visPvtItm, 3)
End Sub
Example 14: Delete all filters currently applied to a PivotTable Field, using the PivotTable.ClearAllFilters Method.
Sub PivotTableFilter14()
Dim PvtTbl As PivotTable
Set PvtTbl = Worksheets(“Sheet1”).PivotTables(“PivotTable1”)
PvtTbl.PivotFields(“City”).ClearAllFilters
End Sub
Example 15: Delete all filters currently applied to the PivotTable, using the PivotTable.ClearAllFilters Method.
Sub PivotTableFilter15()
Dim PvtTbl As PivotTable
Set PvtTbl = Worksheets(“Sheet1”).PivotTables(“PivotTable1”)
PvtTbl.ClearAllFilters
End Sub
Example 16: Delete only manual filters currently applied to a PivotTable Field, using the ClearManualFilter Method. It is a simple way to show all items of a PivotField because this method in effect sets the Visible property to True.
Sub PivotTableFilter16()
Dim PvtTbl As PivotTable
Set PvtTbl = Worksheets(“Sheet1”).PivotTables(“PivotTable1”)
PvtTbl.PivotFields(“City”).ClearManualFilter
End Sub
5. PivotTable.ActiveFilters Property
Use this property to get the current active filter(s), excluding manual filters, in a PivotTable.
Example 17: Count of Active Filters – refer Image 11.
Sub PivotTableFilter17()
Dim PvtTbl As PivotTable
Set PvtTbl = Worksheets(“Sheet1”).PivotTables(“PivotTable1”)
‘set manual filter:
PvtTbl.PivotFields(“City”).PivotItems(“Vancouver”).Visible = False
‘set 3 non-manual filters:
PvtTbl.PivotFields(“Car Models”).PivotFilters.Add Type:=xlCaptionContains, Value1:=“COMPACT”
PvtTbl.PivotFields(“City”).PivotFilters.Add Type:=xlCaptionContains, Value1:=“N”
PvtTbl.PivotFields(“City”).PivotFilters.Add Type:=xlTopCount, DataField:=PvtTbl.PivotFields(“Sum of Sales”), Value1:=3
Worksheets(“Sheet1”).Range(“E1”) = PvtTbl.ActiveFilters.Count
End Sub
Example 18: Get details of each filter applied to a PivotField – refer Image 12 – on the left side is the filtered PivotTable and on the right side is the message box displaying details of each filter. FilterType returns a value for each type of filter applied (refer XlPivotFilterType Enumeration)
Sub PivotTableFilter18()
Dim PvtTbl As PivotTable
Set PvtTbl = Worksheets(“Sheet1”).PivotTables(“PivotTable1”)
PvtTbl.PivotFields(“City”).PivotFilters.Add Type:=xlValueIsBetween, DataField:=PvtTbl.PivotFields(“Sum of Sales”), Value1:=4000, Value2:=6600, Name:=“ValueFilter”, Description:=“Value Between Filter”
For i = 1 To PvtTbl.ActiveFilters.Count
With PvtTbl.ActiveFilters(i)
MsgBox “Filter Item: ” & i & vbCrLf & “Filter Type: ” & .FilterType & vbCrLf & “Filter Field: ” & .PivotField & vbCrLf & “Filter Value1: ” & .Value1 & vbCrLf & “Filter Value2: ” & .Value2 & vbCrLf & “Filter Name: ” & .Name & vbCrLf & “Filter Description: ” & .Description & vbCrLf & “Filter MemberProperty: ” & .IsMemberPropertyFilter & vbCrLf & “Filter Parent: ” & .Parent
End With
Next i
End Sub
6. PivotTable.AllowMultipleFilters Property
PivotTable.AllowMultipleFilters Property (excel 2007 and later): When set to True, you can apply multiple filters to a single PivotField. Default setting is False. When set to False, if any new filter is applied to a PivotField with an existing filter, the new filter will replace the existing one. In vba, when setting is changed from True to False, existing multiple filters applied to a PivotField will be deleted without a warning. Note: the “Allow multiple filters per field” option will be greyed out on a Pivot Table version number 2 (xlPivotTableVersion11) or earlier.