This Excel VBA tutorial explains how to apply multiple criteria in Label Filter of Pivot Table.
Problem with multiple criteria in Label Filter of Pivot Table
There are three kinds of Filters in Pivot Table for each Pivot Filters – Label Filter, Value Filter, Manual Filter. In this tutorial, I will focus on Label Filter, because I use “Department” as an example, which is non-numerical.
Suppose you have the below Pivot Table and you want to show Department Name contains “Fin” and “Aud”.
Click on Department arrow and you can select criteria using Filters. You can see a list of options here for you to make criteria on Department. Now click “Contains…”
You will immediately notice that you can only add one condition (so do other condition options), but we really need to use multiple conditions such as AND, OR
Now go back to the previous option page, you can simply uncheck the HR and IT box. Although you can do the job reluctantly, the action is manual.
Syntax of PivotFilters
Before continue reading, you should have knowledge in creating Pivot Table and change Pivot Table layout. If not, please read the below articles.
Create Pivot Table using Excel VBA
Change Pivot Table Layout using VBA
To add criteria in Pivot Table, we have to use Add Property of PivotFilters.
Below is the syntax of PivotFilters.Add Property
PivotFilters.Add(Type, DataField, value1, Value2, Order, Name, Description, IsMemberPropertyFilter, MemberPropertyField)
Name | Required/Optional | Data Type | Description |
Type | Required | XlPivotFilterType | Requires an XlPivotFilterType type of filter. |
DataField | Optional | Variant | The field to which the filter is attached. |
Value1 | Optional | Variant | Filter value 1. |
Value2 | Optional | Variant | Filter value 2. |
Order | Optional | Variant | Order in which the data should be filtered. |
Name | Optional | Variant | Name of the filter. |
Description | Optional | Variant | A brief description of the filter. |
MemberPropertyField | Optional | Variant | Specifies the member property field on which the label filter is based. |
The first argument Type determines how you want to compare the data in the filter. Some “Type” such as xlValueIsBetween requires two value (Value1, Valu2), but “Value2” does not mean you can add two criteria using one Filter.
Below are the values of Type
Name | Value | Description |
xlBefore | 31 | Filters for all dates before a specified date |
xlBeforeOrEqualTo | 32 | Filters for all dates on or before a specified date |
xlAfter | 33 | Filters for all dates after a specified date |
xlAfterOrEqualTo | 34 | Filters for all dates on or after a specified date |
xlAllDatesInPeriodJanuary | 53 | Filters for all dates in January |
xlAllDatesInPeriodFebruary | 54 | Filters for all dates in February |
xlAllDatesInPeriodMarch | 55 | Filters for all dates in March |
xlAllDatesInPeriodApril | 56 | Filters for all dates in April |
xlAllDatesInPeriodMay | 57 | Filters for all dates in May |
xlAllDatesInPeriodJune | 58 | Filters for all dates in June |
xlAllDatesInPeriodJuly | 59 | Filters for all dates in July |
xlAllDatesInPeriodAugust | 60 | Filters for all dates in August |
xlAllDatesInPeriodSeptember | 61 | Filters for all dates in September |
xlAllDatesInPeriodOctober | 62 | Filters for all dates in October |
xlAllDatesInPeriodNovember | 63 | Filters for all dates in November |
xlAllDatesInPeriodDecember | 64 | Filters for all dates in December |
xlAllDatesInPeriodQuarter1 | 49 | Filters for all dates in Quarter1 |
xlAllDatesInPeriodQuarter2 | 50 | Filters for all dates in Quarter2 |
xlAllDatesInPeriodQuarter3 | 51 | Filters for all dates in Quarter3 |
xlAllDatesInPeriodQuarter4 | 52 | Filters for all dates in Quarter 4 |
xlBottomCount | 2 | Filters for the specified number of values from the bottom of a list |
xlBottomPercent | 4 | Filters for the specified percentage of values from the bottom of a list |
xlBottomSum | 6 | Sum of the values from the bottom of the list |
xlCaptionBeginsWith | 17 | Filters for all captions beginning with the specified string |
xlCaptionContains | 21 | Filters for all captions that contain the specified string |
xlCaptionDoesNotBeginWith | 18 | Filters for all captions that do not begin with the specified string |
xlCaptionDoesNotContain | 22 | Filters for all captions that do not contain the specified string |
xlCaptionDoesNotEndWith | 20 | Filters for all captions that do not end with the specified string |
xlCaptionDoesNotEqual | 16 | Filters for all captions that do not match the specified string |
xlCaptionEndsWith | 19 | Filters for all captions that end with the specified string |
xlCaptionEquals | 15 | Filters for all captions that match the specified string |
xlCaptionIsBetween | 27 | Filters for all captions that are between a specified range of values |
xlCaptionIsGreaterThan | 23 | Filters for all captions that are greater than the specified value |
xlCaptionIsGreaterThanOrEqualTo | 24 | Filters for all captions that are greater than or match the specified value |
xlCaptionIsLessThan | 25 | Filters for all captions that are less than the specified value |
xlCaptionIsLessThanOrEqualTo | 26 | Filters for all captions that are less than or match the specified value |
xlCaptionIsNotBetween | 28 | Filters for all captions that are not between a specified range of values |
xlDateBetween | 32 | Filters for all dates that are between a specified range of dates |
xlDateLastMonth | 41 | Filters for all dates that apply to the previous month |
xlDateLastQuarter | 44 | Filters for all dates that apply to the previous quarter |
xlDateLastWeek | 38 | Filters for all dates that apply to the previous week |
xlDateLastYear | 47 | Filters for all dates that apply to the previous year |
xlDateNextMonth | 39 | Filters for all dates that apply to the next month |
xlDateNextQuarter | 42 | Filters for all dates that apply to the next quarter |
xlDateNextWeek | 36 | Filters for all dates that apply to the next week |
xlDateNextYear | 45 | Filters for all dates that apply to the next year |
xlDateThisMonth | 40 | Filters for all dates that apply to the current month |
xlDateThisQuarter | 43 | Filters for all dates that apply to the current quarter |
xlDateThisWeek | 37 | Filters for all dates that apply to the current week |
xlDateThisYear | 46 | Filters for all dates that apply to the current year |
xlDateToday | 34 | Filters for all dates that apply to the current date |
xlDateTomorrow | 33 | Filters for all dates that apply to the next day |
xlDateYesterday | 35 | Filters for all dates that apply to the previous day |
xlNotSpecificDate | 30 | Filters for all dates that do not match a specified date |
xlSpecificDate | 29 | Filters for all dates that match a specified date |
xlTopCount | 1 | Filters for the specified number of values from the top of a list |
xlTopPercent | 3 | Filters for the specified percentage of values from a list |
xlTopSum | 5 | Sum of the values from the top of the list |
xlValueDoesNotEqual | 8 | Filters for all values that do not match the specified value |
xlValueEquals | 7 | Filters for all values that match the specified value |
xlValueIsBetween | 13 | Filters for all values that are between a specified range of values |
xlValueIsGreaterThan | 9 | Filters for all values that are greater than the specified value |
xlValueIsGreaterThanOrEqualTo | 10 | Filters for all values that are greater than or match the specified value |
xlValueIsLessThan | 11 | Filters for all values that are less than the specified value |
xlValueIsLessThanOrEqualTo | 12 | Filters for all values that are less than or match the specified value |
xlValueIsNotBetween | 14 | Filters for all values that are not between a specified range of values |
xlYearToDate | 48 | Filters for all values that are within one year of a specified date |
Apply single criteria in PivotFilters
To filter department that contains string “Fin”
ActiveSheet.PivotTables("PivotTable3").PivotFields("Department").PivotFilters.Add Type:=xlCaptionContains, Value1:="Fin"
The above example has the same effect as typing “Fin” in Label Filter
Cancel criteria in Label Filter
Method for PivotFields | Explanation |
ClearValueFilters | Clear only Value Filter |
ClearLabelFilters | Clear only Label Filter |
ClearManualFilters | Clear only Manual Filter (items your manually select or uncheck) |
ClearAllFilters | Clear all Value, Label and Manual Filters |
The below example cancels all Filters for pivot field “Department”.
ActiveSheet.PivotTables("PivotTable3").PivotFields("Department").ClearAllFilters
In order to cancel all Filters of all PivotFields, loop through all PivotFields in PivotTable
Public Sub cancelAll() For Each ptf In ActiveSheet.PivotTables("PivotTable1").PivotFields ptf.ClearAllFilters Next End Sub
Apply multiple criteria in PivotFilters
Same old problem as in user interface of Label Filter, we cannot add multiple criteria under one Filter even in VBA.
To work around, we need to simulate the effect of “checking” and “unchecking” the box but also adding the function to do the “Contain” filter.
The department name (Audit, Finance…) under a “Department” (a PivotFields) is referred to as “PivotItems”. Each PivotFields has its own array of PivotItems.
We can loop through each PivotItems (Audit, Finance…) in PivotFields “Department” to check the name of each department.
Sub Macro1() With ActiveSheet.PivotTables("PivotTable3").PivotFields("Department") For i = 1 To .PivotItems.Count If InStr(1, .PivotItems(i), "Fin")<>0 Or InStr(1, .PivotItems(i), "Aud")<>0 Then .PivotItems(i).Visible = True Else .PivotItems(i).Visible = False End If Next i End With End Sub
What I did in the above code is to loop through each PivotItems (Audit, Finance…) under “Department”, and check the name to see if it contains “Fin” or “Aud” using INSTR function (return 0 if substring not found, return the position of first occurrence if found) . If found, show them, otherwise hide them.
Here is how you can simulate the rest of the criteria in Label Filter.
Equal | PivotItems(i) = “xxx” |
Does Not Equal | PivotItems(i) <> “xxx” |
Begins With | left(PivotItems(i),len(“xxx”)) = “xxx” |
Does Not Begin With | left(PivotItems(i),len(“xxx”)) <> “xxx” |
Ends With | right(PivotItems(i),len(“xxx”)) = “xxx” |
Does Not End With | right(PivotItems(i),len(“xxx”)) <> “xxx” |
Contains | Instr(1,PivotItems(i),”xxx”) <> 0 |
Does Not Contain | Instr(1,PivotItems(i),”xxx”) = 0 |
Select specific number of PivotItems
Instead of using text to filter, we can specify the how many PivotItems we want to return. In the below example, I want to select only the 2nd to 5th PivotItems in alphabetic order.
Sub Macro1() With ActiveSheet.PivotTables("PivotTable1").PivotFields("Department") For i = 1 To .PivotItems.Count If i >= 2 And i <= 5 Then .PivotItems(i).Visible = True Else .PivotItems(i).Visible = False End If Next i End With End Sub
In the below example, I want to exclude the 1st to 5th department.
Sub Macro1() With ActiveSheet.PivotTables("PivotTable1").PivotFields("Department") For i = 1 To .PivotItems.Count If i >5 Then .PivotItems(i).Visible = True Else .PivotItems(i).Visible = False End If Next i End With End Sub
In the below example, I want to exclude the 1st to 5th department.
Sub Macro1() With ActiveSheet.PivotTables("PivotTable1").PivotFields("Department") For i = 1 To .PivotItems.Count If i >5 Then .PivotItems(i).Visible = True Else .PivotItems(i).Visible = False End If Next i End With End Sub
In the below example, I want to include the 1st to 5th department plus Finance department
Sub Macro1() With ActiveSheet.PivotTables("PivotTable1").PivotFields("Department") For i = 1 To .PivotItems.Count If i <=5 OR InStr(1, .PivotItems(i), "Fin")>0 Then .PivotItems(i).Visible = True Else .PivotItems(i).Visible = False End If Next i End With End Sub
Outbound References
http://msdn.microsoft.com/zh-tw/library/office/ff193582%28v=office.14%29.aspx
I am trying to use the XlPivotFilterType Enumeration in a macro according to this page in the Microsoft MSN website
https://msdn.microsoft.com/en-us/vba/excel-vba/articles/xlpivotfiltertype-enumeration-excel
This is the macro I am using:
—–
Sub Macro1()
‘
‘ Macro1 Macro
‘
Dim PvtTbl As PivotTable
Set PvtTbl = Worksheets(“Sheet1”).PivotTables(“PivoTable1”)
‘delete all filters currently applied to the PivotTable, using the PivotTable.ClearAllFilters Method
PvtTbl.ClearAllFilters
PvtTbl.PivotFields(“Month”).PivotFilters.Add Type:=xlAllDatesInPeriodJanuary
‘
End Sub
—–
I want to add another month, how to do it? like Feb or Mar or both Feb and Mar, so the end result to be Jan, Feb, Mar …etc
These are the items for the whole year
Thanks
Here you go
Sub Macro1()
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Date")
For i = 1 To .PivotItems.Count
If .PivotItems(i) = "Jan" Or .PivotItems(i) = "Feb" Then
.PivotItems(i).Visible = True
Else
.PivotItems(i).Visible = False
End If
Next i
End With
End Sub