How do I loop through the filter items and hide items in an Excel PivotTable using the Data Model?

Question

I have been working with normal PivotTables in VBA, but I recently found some features on the Pivot Tables using the Data Model that I really like--mainly 'Distinct Count'. I have some code in a normal pivot table which filters the table for records 'Like' a string, and it works perfectly. How might I convert this code to the Pivot Table using the Data Model?

With ActiveSheet.PivotTables("Metrics").PivotFields("Reference number")
    .Orientation = xlPageField
    .Position = 1
    .EnableMultiplePageItems = True

    For i = 1 To .PivotItems.Count
         If .PivotItems(i).Name Like "*oran*" Then
            .PivotItems(i).Visible = False
        End If
    Next i

End With

Here is the code that is created when I record a macro and select the items to display manually under the Data Model:

ActiveSheet.PivotTables("Metrics").PivotFields("[RawData].[Status].[Status]"). _
VisibleItemsList = Array("[RawData].[Status].&[apple_434]", _
"[RawData].[Status].&[banana_689]", _
"[RawData].[Status].&[orange_1346]", _
"[RawData].[Status].&[orange_1454]")

This is the direction I am heading, but I am having some trouble accessing the VisibleItemsList Array:

With ActiveSheet.PivotTables("Metrics").PivotFields("[RawData].[Status].[Status]")    
    For i = 0 To UBound(.VisibleItemsList)
        If i Like "*oran*" Then
            i = ""
            Debug.Print i
        End If
    Next i 
End With

The output i is numeric 0,1,2,3,4 --not text, and the numbers do not seem to correspond to the number of items in the filter list. I can't figure out how to access the items, so I can show or hide the ones I want using code. I will be honest that I have not been working with arrays for very long.

Does it have to be VBA? Can you install this free add-in and leverage the Filter List feature to do this for you? http://olappivottableextend.codeplex.com/wikipage?title=Filter%20List&referringTitle=Home — GregGalloway, Mar 22 '16 at 00:51
I had someone reach out to me on a similar request in response to some non-OLAP filtering code I'd posted at http://dailydoseofexcel.com/archives/2013/11/14/filtering-pivots-based-on-external-ranges/ It took me a long time and a lot of hacks to accomplish via VBA. I'm building by code into a commercial addin that also handles complex wildcards and exclusions and can be pointed at a range of search terms. Sneak preview at http://dailydoseofexcel.com/archives/2015/11/17/filtering-pivottables-with-vba-deselect-slicers-first/ . Leave a comment there if you want to know more. — jeffreyweir, Mar 22 '16 at 01:57
-GregGalloway, It has to be VBA and I don't have the flexibility to install an add-in. If I did, that link would have solved my problem. Thanks for sharing. — Bo_, Mar 22 '16 at 13:50
-jeffreyweir, I read through your addin, and I like your work. Your last post on the 'Set up a filter on an "OLAP"'mentions that you are working on incorporating OLAP into your addin. Have you had any luck? — Bo_, Mar 22 '16 at 13:50

score 0 · Accepted Answer · edited May 23 '17 at 12:07

I ended up using slicers to filter the data.

Dim sC As SlicerCache
Dim sL As SlicerCacheLevel
Dim aArray() As Variant

'create a slicer cache. I just used a recorded macro to get the cache code
ActiveWorkbook.SlicerCaches.Add2(ActiveSheet.PivotTables("Metrics"), _
    "[RawData].[Status]").Slicers.Add ActiveSheet, "[RawData].[Status].[Status]", _
    "Status", "Status", 141.75, 424.5, 144, 198.75

Set sC = ActiveWorkbook.SlicerCaches("Slicer_Status")
Set sL = sC.SlicerCacheLevels(1) 'this will start with the first item in the slicer

    With sL
        For i = 1 To .Count
            If sL.SlicerItems.Item(i).Name Like "*oran*" Then
                GoTo nextiteration 'this will skip over anything
                                   'like oran when saving to the array
            End If

            ReDim Preserve aArray(0 To i) As Variant
            aArray(i) = sL.SlicerItems.Item(i).Name

            nextiteration:
        Next i
            sC.VisibleSlicerItemsList = aArray 'this set the visible items 
                                               '= to the array you just created
            'ActiveSheet.Shapes("Status").Visible = False 
            'to hide this slicer, uncomment the line above
    End With

This Post by Paul te Braak provided the bulk of the solution. I also used this tutorial for some help with saving items to arrays. This stackoverflow answer also helped me when I needed to work with dynamic arrays. Thanks to -GregGalloway and -jeffreyweir: when looking through the links you provided, I got the idea to search for solutions using slicers.

score 0 · Answer 2 · answered Apr 16 '19 at 22:30

Hey I know this is an old question, but I recently came up with a good solution for this:

<your other code goes here>

dim v as Variant, xlPT as Excel.PivotTable, str as String
str = vbNullString
set xlPT = xlBook.PivotTables("MyPivot")
with xlPT
    for each v in .PivotFields("[table].[field]").PivotItems
        select case Right(Left(v.Name, Len(v.Name) - 1), 4) 'my variables are 4-char strings
                                ' But you can manipulate this piece to match your need
            case "1234", "4312", "4321", "7462", "etc."
                str = str & v.Name & ";"
        end select
    next v
    .CubeFields("[table].[field]").PivotFields(1).VisibleItemsList = Split(str, ";")
end with

<the rest of your code goes here>

The key bit of knowledge here was that the CubeField object contains a PivotFields collection with just 1 member, and we can work with the VisibleItemsList in it.

Hope this helps someone in the future. Happy SO'ing

How do I loop through the filter items and hide items in an Excel PivotTable using the Data Model?

2 Answers2