VBA Control Pivot Table

[Tiger Chen]

[vba] For Each strPVItem In .PivotFields(strTitleINCI).PivotItems
If Not strPVItem.Name Like "*" & strInciName & "*" Then
strPVItem.Visible = False
Else
strPVItem.Visible = True
End If
Next
[/vba]

I created a pivot table with VBA Code. I would like to show only a few relative items among 5000 items. Now, I have to use the code above to set the visibility. It takes a long time.

Is there a way can set it all in one time, just like set it in the dialogue box manually?

By the way, I record the operation of setting the visibility in dialogue box. VBA will record my operation one item after another. It takes a second to record (to set the visibility manually) the operation. However, when I run the recorded code again, it takes a long time!!!

Thanks,

[JWhite]

If you find an answer to this, let me know. I have also found that looping through pivotitems is very slow.

As you've seen, it's fast manually. When you first look at the filter dropdown, everything is selected. You un-click on "Select All" and that unchecks every item and then you just check the ones you want. I recorded a macro to find out how to issue one command to un-check the "Select All" and found that the VBA code sets EVERY pivotitem to "visible = false" individually. And if you execute that code it's a lot slower than unchecking that one box.

So apparently there's no way around it. If you just want to show a few items you have to set all the rest to "visible = false". And it is slow. It's faster if you set "Manual Update = True" but other than that I haven't found anything. I'd love to learn another approach. Anybody?