List all pivot items in a pivotfield but only those visible (there is a filter on the field)

[tweedi]

Hi All,

I have a pivot table with one of the pivotfields on which there is a filter. The filter never changes, however depending on the source data, some items might be visible in the table and some might not.

For example let's say that the pivot field is called "Fruit" and that there is a filter on this field to keep only items called "Banana" and "Mellon".

Let's say that this week in the data source there is no mellon but only bananas.

If I do:

For each pitem in PVTfruits.pivotfields("Fruit")
If pitem.visible = true then msgbox pitem
end if
next pitem

It will display a msgbox for both Mellon and Banana (because they are ticked in the filtered), but this is not the result I am trying to achieve, It should only show "Banana" because this week (once the pvt is refreshed) there is no mellon in the source data.

Does anyone have an idea of how I can do this?

Many Thanks for your help
Tweedi

 

[Kuljack]

Just right-click the pivot table >> Data tab >> Select None for the filter option under Retain items deleted from the data source, and refresh the pivot.

 

[tweedi]

Hi Kuljack, Unfortunately this does not seem to resolve the issue.

Indeed vba still lists me all the items that are ticked in the filter but I would like to list only the ones that re visible in the pivot table (i.e. the ones that are ticked in the filter and for which there are values)

Anybody can help?

 

[Kuljack]

Maybe I do not understand what your goal is here.

By setting this function to None, when the data is refreshed Mellon should no longer be ticked because it would no longer be present. Only banana should remain, unless you have other criteria?


Is there perhaps Banana, Mellon, and Cabbage in your data - where cabbage is always unticked since in this example it's a vegetable and you only want fruits with available data ticked?


To do what you want, it would probably be best to have a helper Macro assist, something like the following (obviously this is not the code):

Array = Fruits listed

On Worksheet Refresh update Pivot Filter to -> Array of fruits