Using arrays to Filter Pivot table labels

Question

Does anyone know a way to filter a PT label row based on an array of values. I tried this but got an error:

Run-time error '5' - invalid argument or procedure call

Sub test()

Dim PT As PivotTable
Dim PF As PivotField
Dim StrArr() As Variant

StrArr = Array("89905-0496", "89905-0497", "89907-0492", "89587-0499", "89585-0498")

Set PT = Sheet5.PivotTables(1)
Set PF = PT.PivotFields("[HFM LEDGER ACCOUNTS].[CONCATENATED ACCOUNT].[CONCATENATED ACCOUNT]")

PF.ClearLabelFilters
PF.PivotFilters.Add2 Type:=xlCaptionEquals, Value1:=StrArr

End Sub

Out of interest does this work if you iterate through the array and add each value individually? I'd be interested to see if it is possible to add an array directly though! — MiguelH, Feb 02 '18 at 15:35
What if you try this n `PivotFilters.Add Type:=xlCaptionEquals, Value1:="z"` Note `Add` not `Add2` — MiguelH, Feb 02 '18 at 15:56
Yea same error. I guess it's a Pivot Table limitation. I also tried using Add and same error. Oh well — Mike Mirabelli, Feb 02 '18 at 16:10
Perhaps try adding to each field in turn. Not sure updating all 3 simultaneously will work! Try it on one to start with so that the logic is as simple as possible. Extend the logic once you get a working example. — MiguelH, Feb 02 '18 at 16:12
I'm not sure what you mean to be honest. I've tried with a loop. Is there another syntax you are thinking that could work ? like altering of an existing label filter maybe? — Mike Mirabelli, Feb 02 '18 at 16:21

warner_sc · Accepted Answer · 2018-02-02T17:07:31.100

This is my closest approach, it is UGLY! but works:

Try with this sample data:

Name    Number
lol     2
cheese  2
foo     6
ball    5
lol     5
cheese  3

Make a pivot table using the sample data that will sum the numbers of each "Name"

Lets say you want to filter the "foo" and "lol" values in the pivot field "Name".

Here is the code:

         Option Explicit

            Sub add_filters_to_pivot()

            Dim filters As Variant
            Dim pivot_field As PivotItem
            Dim pivot_filter As String
            Dim element As Variant
            Dim filter_exist As Boolean

            filters = Array("foo", "lol")

            ActiveSheet.PivotTables("PivotTable1").PivotFields("Name").ClearAllFilters

                For Each pivot_field In ActiveSheet.PivotTables("PivotTable1").PivotFields("Name").PivotItems

                    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Name")

                        pivot_filter = pivot_field
                        filter_exist = filter_array(filters, pivot_filter)

                        If filter_exist <> True Then
                        .PivotItems(pivot_filter).Visible = False
                        End If

                    End With

                Next pivot_field

            End Sub

            Public Function filter_array(array_to_filter As Variant, filter_string As String) As Boolean

            Dim filtered_array As Variant
            Dim i As Integer

            filtered_array = Filter(array_to_filter, filter_string, True, vbTextCompare)

            On Error GoTo is_false

            For i = 0 To (UBound(filtered_array) + 1)
                If filtered_array(i) = filter_string Then
                filter_array = True
                Exit Function
                End If
            Next i

            is_false:
                filter_array = False

            End Function

Oh I see what you did that is clever. That's for the provided I will definitely keep this if needed for future projects. — Mike Mirabelli, Feb 02 '18 at 18:25

score 0 · Answer 2 · answered Feb 02 '18 at 18:37

If your PivotTable is based on OLAP, then yes, you can filter a PivotTable on an array in one assignment. This is really handly because you can feed such an PivotTable either an array of things you want to keep, or an array of things you want to filter out. See Pivot Table filter out only 1 option

It's easy to make your PivotTable the OLAP kind...simply select 'Add to Data Model' when you initially create the PivotTable. In other words, Power Pivot PivotTables are OLAP PivotTables.

If you have a 'normal' PivotTable, then you need to iterate through it. There are many potential bottlenecks when doing this, and if you don't code around them, then it might take minutes to iterate through a PivotField with as little as 20,000 PivotItems in it. See my post at http://dailydoseofexcel.com/archives/2013/11/14/filtering-pivots-based-on-external-ranges/ for more on what to watch out for, and then see my answers at the following links that program around these bottlenecks:

Filtering on SlicerItems

Filtering on PivotItems

Using arrays to Filter Pivot table labels

2 Answers2