VBA / Pivot Tables / Looping through PivotItems takes too long

[LEXCERM]

Hi,

So, I have one line of code which quickly hides a value in a filter:-

ActiveSheet.PivotTables(1).PivotFields("ABC").PivotItems("Test").Visible = False

However, I need to do the reverse and only show value "Test" in the filter. This will work, but takes far too long:-

Set pt = ActiveSheet.PivotTables(1).PivotFields("ABC")
For Each pi In pt.PivotItems
    If pi.Name = "Test" Then pi.Visible = True Else pi.Visible = False
Next pi

Is there a way to use the same method as in the first example so that it hides all values except "Test"?

Thanks in advance.

 

[aaewalsh]

Try this:

The first line sets the visibility of all items in the pivot field to False, and the second line sets the visibility of the "Test" item to True. This will hide all items in the pivot field except for "Test".

ActiveSheet.PivotTables(1).PivotFields("ABC").PivotItems.Visible = False
ActiveSheet.PivotTables(1).PivotFields("ABC").PivotItems("Test").Visible = True

Alternatively you can use the following method:

ActiveSheet.PivotTables(1).PivotFields("ABC").PivotItems. _
    EnableMultiplePageItems = True
ActiveSheet.PivotTables(1).PivotFields("ABC").PivotItems("Test"). _
    Visible = True

 

[LEXCERM]

Thanks for the reply.

I get this error when executing first lines of each example:-

 

[aaewalsh]

are you getting the same error for both

 

[LEXCERM]

are you getting the same error for both

Yes, for both:
PivotItems.Visible = False
and
EnableMultiplePageItems = True.