VBA commands to filter pivot tables

I have a pivot table containg 65,000+ combinations of "tools" and corresponding "charts"

For instance: Tool 1 may be associated with charts A, B, C, D, E

The pivot table is useful because I can drag and drop the pivot fields, and show that chart A is associated with tools 1, 2, 3, 4, 5

etc.

The actual question:

When I record a macro and select/deselect all items within a pivot field, the macro shows one line of code to manually show or hide EACH individual chart:

Please Login or Register  to view this content.

If I'm trying to use a macro to hide all the charts except for a few which are stored in an array (output from another macro - the selection of charts will vary, thus using VBA to filter based upon a variable and not doing it manually), then I have a macro with thousands of lines of code, and it becomes useless if my selection of charts changes, or the query of 65,000 combinations is updated.

I can't seem to find an easy way to say something like:

Within the PivotField "Chart", hide all (Visible = False) PivotItems except for these: [chart names in my array]

I have been playing around with some loops and might be able to do it, but I also need to minimize how long it takes to macro to scan through 65,000 rows and examine each cell before determining whether or not to hide it. Is there a "PivotItem(All).Visible = False" or something that I am unaware of?

I hope what I'm asking is somewhat clear... I need an easy way to only display a small portion of the 65,000 combinations in my table based upon a selection generated by another macro and stored in an array.

Any advice? Thanks a bunch

i would reccomend not to use a pivot table but a delimited set or rows. It will be easier to set the macro after.

Could you expand that advice a bit please, I'm not sure I follow the benefits.

I have 2 columns of data: Tools and Charts
There are 65,000 rows of combinations which fall under these columns.

My reasoning was to populate a pivot chart, which is flexible in showing which tools are associated with which charts, as well as which charts are associated with which tools.
Using filters means I can reference any of the 65,000 combinations depending upon what my array is- the array will store a grouping of charts--(A,B,C) for instance--based upon what a different macro from a different source of data spits out, and without having to delete the data I'm not concerned with for that particular grouping of charts.

Say my array contains chart names (A,B,C), I want to be able to display a pivot chart filtered to only show A, B, and C, so that I can easily view which tools are associated with each individual chart, and which individual chart is associated with which tools.

I hope this makes sense.

I am definitely open to easier/smarter ways, I just don't understand what you mean exactly.

What should I do with the raw data- 2 columns, 65000 rows

can you upload the file?

pivot test.xlsm

I can't upload the actual data set. I can only use examples.

Here is a simplified example. I just created a macro which seems to work, I just haven't tried expanding it to recognizing multiple chart names, to recognizing the chart names from an array variable which can change in size, or to a data set containing much much more data than this one.

This macro requires examining each pivot item then deciding whether or not to turn it off. Is there maybe a way to turn them all off, find the desired ones, and turn them on in faster/more efficient way?

Please Login or Register  to view this content.

This seems to work alright, at least for small data sets.

Please Login or Register  to view this content.

Is there a way to say if pi = any of the arguments in ChartGroup?
Other than storing the size of the array and doing a For loop for that many iterations. Is there a simple syntax?

simpler syntax i dont think so. Well, from my begginner eyes at least.