Modify hide/unhide columns via Pivot Table

Its been a while since I've worked with VBA, and trying to learn if its possibly to modify this script I found a while back located here and listed below:

h**ps://www.excelcampus.com/vba/hide-unhide-columns-slicer-filter/

Essentially, the script will let you hide and unhide columns based on Pivot Filter criteria and it works pretty well in that you can select multiple items and it will 'filter' or hide and unhide those columns.

And on that link, there is mention also of being able to use it with Multiple Pivot tables which I have done.

But I'm trying to see about modifying the code to use 1 Pivot table with Multiple Filter fields instead of 2 or more pivot tables each with just 1 filter field. Thus, I think I need to somehow loop the pivot table filters of a pivot table.

I'm trying to do this so that when using with slicers, the use of 1 Pivot table will then eliminate those items as you filter different fields.


Attached is a sample file where I have a pivot table with filters for Region, State, City, Market, and Sales. And each of those has defined named range associated with it.

Please Login or Register  to view this content.

I may have made some little bit of progress. I found some info here https://stackoverflow.com/questions/...e-from-vba-cod
that got me to figure out how to list all of the selected items from the filtered field in a pivot table.

I think the next necessary steps may be to?

1. Combine all my named ranges (ie header rows) into 1 large range and maybe place in an array.
2. Put all of the listed items from the filters into an array.
3. Compare the arrays and isolate the differences or ones not found in range.
4. Then hide those differences/columns

Please Login or Register  to view this content.

I think I've made some pretty good progress on this.

What I am currently doing is using a Union to combine the named Ranges together. Then I am getting the Unselected or False values from all of the Pivot Table filter items. I then loop all of those filter items comparing the value from the combined range. If there is match, add to a new range. Then when done, hide all of the unmatched columns.

This seems to be working pretty good so far, but I'm sure the code can be cleaned up further, and I have only tested so far with 3 pivot table filter fields.

Updated code and Sample attached.

Please Login or Register  to view this content.