Turning on and off pivotItem visibility error

Question

I'm trying to filter items from a pivot table.

Public Sub Test()

    Dim pi As PivotItem
    Dim pt As PivotTable
    Dim pf As PivotField

    Set pt = Worksheets("Collections").PivotTables("Collections")
    Set pf = pt.PivotFields(1)

    pt.ClearAllFilters

    For Each pi In pf.PivotItems
        Debug.Print pi.Name
        If pi.Caption = "00087" Then
            pi.Visible = True
        Else
            pi.Visible = False
        End If
    Next pi

End Sub

If figured it'd be fairly straight forward, however, I get a

Runtime error '1004' Unable to set the visible property of PivotItem class

From what I understand, I can't set every one to false. But, I did a debug.print and all the visible properties are true to begin with, so I can't see why setting a .Visible property to false would be an issue.

I replicated your code and run it with a small data example and had no errors, can you provide a little data sample.. — warner_sc
Sure, @hot_potato would it being driven from an OLAP cube cause an issue? — user1093111
I've never used a OLAP data cube before, but i would consider checking the formats of the data or trying to filter using a character string like "Dog", check my answer for more detail... — warner_sc

jeffreyweir jeffreyweir · Accepted Answer · 2018-03-28T09:18:15

If this is an OLAP PivotTable, then you can't iterate through the PivotItems collection. Instead, fire up the macro recorder, manually filter on the item of interest, then inspect the code that is produced.

If it's a PageField, you'll see something like this:

ActiveSheet.PivotTables("Pt1").PivotFields("[Table1].[test].[test]").ClearAllFilters
ActiveSheet.PivotTables("Pt1").PivotFields("[Table1].[test].[test]"). _
        CurrentPageName = "[Table1].[test].&[2]"

If it's a Row or Column field, you'll see something like this:

ActiveSheet.PivotTables("Pt1").PivotFields("[Table1].[test].[test]"). _
    VisibleItemsList = Array("[Table1].[test].&[3]")

As you can see, you don't need to iterate in either case, and the code that gets spat out should be enough for you to work out what to do.

If it's NOT an OLAP PivotTable, then you must be inadvertently setting them all to false...possibly because the .caption has been changed and so doesn't match the .name, or possibly because the "00087" item doesn't exist in your data. So what's happening is that it's not finding a PivotItem with the caption of "00087" in the PivotItems, and when you try to set the last one to hidden, there's no visible items left.

But regardless, iterating through PivotItems is horribly inefficient, and there's a much better way to approach this given you just want to filter on one item: Make the field of interest a PageField (i.e. put it in the Filters part of the PivotTable) and then set .EnableMultipleItems to FALSE and then simply set .CurrentPage = "00087"

To get the exact syntax, fire up the macro recorder, filter on the one item, and look at the code the macro recorder spits out.

If you don't want to change the layout of your PivotTable (i.e. can't make the field a PageField because you want it to remain in the ROWS or COLUMNS area) then see my answer at https://stackoverflow.com/a/39604425/2507160

For future reference, note that if you ever iterate over PivotItems, set the PivotTable's .ManualUpdate to TRUE while you make your changes, and set it back to FALSE afterwards to avoid the PivotTable refreshing after each and every change.

For more on efficiently programming PivotTables, check out my blog post at http://dailydoseofexcel.com/archives/2013/11/14/filtering-pivots-based-on-external-ranges/

Turning on and off pivotItem visibility error

2 Answers