Unit 7 – Pivot Table VBA

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)

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

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

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.

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