Project using Advanced filter, Userform, and Macros

I am not quite sure this is what you want, but I am taking advantage of Excel Tables and slicers to accomplish what I think the requirement is. I converted your data into an excel table. Here is more information on excel tables: http://www.utteraccess.com/wiki/Tables_in_Excel.

One of the advantages of Excel Tables not mentioned in the wiki is that they can also be assigned slicers (glorified filters). Do a web search for slicers. They are very easy to set up and are very intuitive. The slicers take the place of your advance filters. They also "cascade" meaning that if you make a selection in one slicer, it affects the available selections in other slicers. For example: if you select Team Alpha, some of the other slicers' items will be grayed out meaning they are not available for selection.

If you check over on the Data Tab, you will see that the table is filtered in accordance with the slicer selections. You can do multiple selects using the CTRL and SHIFT keys. You can clear an individual slicer by clicking on the funnel icon on the top left.

The Copy Data button copies over the visible rows in the data table into the results table.

The Clear Source button clears the filters on the data table and consequently (as in automatically, I did not have to write code for it) clears the slicers that look at the table. Slicers and table filters work together, set a table filter and it sets the slicer; set a slicer and it sets the table filter.

Excel does all the "heavy lifting" - the code is very short.

Please Login or Register  to view this content.

The ModClearTable5 module is some "Standard" code I wrote to clear table filters and clear table contents. I use it frequently so I saved it into an importable module.