How to hide many pivotitem at once

Question

Is there a way to hide a list of PivotItems in a single command?

I'm Using Excel2010 & standard pivot, not OLAP data source.

I'm looking for a way to significantly speed up the code execution. I already suspended the recalculation, the video refresh, set .ManualUpdate = True, ...

I'm looking for something like pivotfield_list.hide (or pivotfield_list.visible = False), or even better Hide_list (pivotfield ("xx"), pivotfield_list).

Let me explain better. If you select only a couple of entries in a pivotfield via the user interface you get the result quickly. On the contrary, if this operation is performed with the macro recorder on, this is translated with n commands like:

With ActiveSheet.PivotTables ("pDoc"). PivotFields ("pir_L4")
    .PivotItems ("1208 Costs"). Visible = False
    .PivotItems ("1221 - series"). Visible = False
    .PivotItems ("1231 - ILS"). Visible = False

.... and so on

If you then re-run the macro, the time is much greater (in my table there are many possible values to be negated).

Since I can do a massive operation via GUI it seems strange that I can not replicate it from VBA. I have been looking for a solution a lot on internet without success, any suggestions?

PS. I have found that in the case of contiguous values ​​of a pivotitem the following command can also be carried out:

range.delete

But this is not my case (not contiguous values also in for different). It seems that you do not reach the goal even passing through the sliders and their properties.

Answer 1

The quickest way to hide all PivotItems but one is to either make the PivotField of interest a Page Field and then use the .CurrentPage property to instantly filter on that one item.

If you don't want the PivotField in the Page Fields area, then set up a 'Master' PivotTable somewhere out of sight, putting the field of interest in the master as a PageField in the 'Master' pivot, and connect the Master PivotTable to your PivotTable ('Slave') with a Slicer. Then you can simply change the .CurrentPage property of the Master, which will then instantly filter the Slave via the Slicer.

Have a look at my answer at VBA code to filter a pivot table based on the value in a Cell as well as the function I wrote at unable to get the visible property of the pivotitem class