Excel VBA multiple criteria in Label Filter of Pivot Table

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”.

pivotFilters_01

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…”

pivotFilters_02

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

pivotFilters_03

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.

pivotFilters_04

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

pivotFilters_03

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.

pivotFilters_04

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

 

2 thoughts on “Excel VBA multiple criteria in Label Filter of Pivot Table

  1. 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

    1. 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

Leave a Reply

Your email address will not be published.