Displaying all Applied Filters

Hi Kim,

Great question. See the attached file and complete code that follows which is based on similar code to yours. I would give credit to the author, but I lost the original link.

Please note that there can be several ranges that can use AutoFilter on one sheet, but only one AutoFilter range can be active on one sheet at a time (at least in Excel 2003). If using more than one range per sheet, they SHOULD NOT BE side by side, because when AutoFilter hides rows, it may look like a non-AutoFilter range is being filtered.

AutoFilter can be ON at the same time on several different sheets in the same workbook

I called the function 'GetAutoFilterCriteria()' and created a SHORTCUT function 'AFC() ' that does the same thing to allow for less typing when implementing the function in a cell. Even though the function is declared Volatile (which means a recalculation occurs any time any cell on the sheet needs calculation), recalculation may not occur when switching between AutoFilter OFF and AutoFilter ON when there are no Autofilter criteria.

Lewis

Please Login or Register  to view this content.

Thank you both very much, Lewis this is great and we really don't use a filter for more than two items per column so this will work well. The dates not formatting in the filter I will try to play with. Holger, the why is because some of the spreadsheets we have are very long and have many columns (I believe through column "mm" at this time), if we only had a few columns would be silly to try to work to get this. The Business Analyst is looking for a quick way to see what the filters are and what was chosen - in what order - so she can make sure the reporting is accurate when switching criteria. Currently she removes all of the filters she has set and redoes them. If she knew from the previous weeks reporting where the filtering was left off (as an example) it would just help make her life easier. Thanks again to both of you I really appreciate the replies.

The following may help you with date conversion:

Please Login or Register  to view this content.

Lewis

Lewis, you're terrific thank you! I will try this today. Have a terrific rest of your day!