Show autofilter criteria

  • Dave

    You posted this code and it works well

    could u advise how to display just the criteria ie no heading, no :, no =, and when it displays the criteria could it fill the cell with a colour. When filter is set to "all" give a blank cell

    This would over come lots complaints from operators not realizing that filters are on because they can not find the silly blue button.

    Are microsoft aware of is and are they changing it in the new release.

    Andy Hopr u can help

  • Re: Show autofilter criteria

    Display/Show AutoFilter Criteria

    Excel's AutoFilter is one of Excel's most useful features. However, one small draw-back is it's hard top tell the criteria being used at a glance. The custom Excel function below can be used to display the criteria being used for each column of the table that has AutoFilter applied. All you do is ensure you have at least 2 rows above your table, then add the custom function to each cell 2 rows above the column heading. See below;

    The custom function is used in each cell shown above like;

  • Re: Show autofilter criteria

    Forum rules

    I have added code tags for you in this post.Please read the rules you agreed to particularly concerning Code tags & their use. Thanks

  • Re: Show autofilter criteria


    Thanks for tidying up code could u help adding to it to give the results to my first query i.e changing the header to a colour when the filter is selected etc
    Tried the code u suggested did not give what i needed


  • Re: Show autofilter criteria

    Just a passing comment...
    The AutoFilter_Criteria function posted earlier in this thread, and featured in this months OzGrid Excel June 2006 Newsletter, is remarkably like one I wrote a couple opf years ago.

    The volatile statement can result in the functions executing needlessly many thousands of times, making non-trivial autofiltered lists run very slowly, especially if code to do other things is piggy backed onto this function.

    I got around this by ensuring the function was called from the worksheet like this...


    =AutoFilter_Criteria (C3:C4)

    where C3 is the header row. The inclusion of C4 acts as a "changing precedent cell", therefore forcing a recalc of the cell containing the formula only when one of the filters in the autofilter changes, making the worksheet run much faster.

    I've not tested it, but I think the code posted above will run fine with the volatile statement removed and no other changes, provided a 2 cell range is passed to it as I described.

    Does anyone know how to implement a similar function to highlight when a pivot filter is filtering data??

  • Re: Show autofilter criteria

    the method to avoid "Application.Volatile" seems like an attactive alternative if it can reduce the recalc/UDF trigger to only a change in auto filter.

    I tried the method described by BruceMutton:

    I did this-
    1. commented out the "Application.Volatile" line
    2. changed the formula input in the cell with the function from " =AutoFilter_Criteria(D2:D2) "
    to " =AutoFilter_Criteria(D2:D3) "

    but that does not work. the cell now shows #VALUE!
    is there something more to be done?

    Using the original form with "Application.Volatile".....
    a problem I noticed was that Autofilter "Show All" does not cause the UDF to recalc/update,
    only if create my own UDF for ShowAll which has a "Calculate" as the last command.

    Any suggestions on addressing these?


  • Re: Show autofilter criteria

    Not sure if Daves post answers your questions.
    I forgot to mention that you also need to declare the range variable with the UDF declaration.

    Here is my old code that is a bit more long winded than the Ozgrid code, and somewhat tailored to a particular situation but has the same general structure. I like the single variable passing approach, rather than the two I use. Mine converts date serial numbers to a format you and I can understand.

    Call the code below with

    in the cell formula.
    I place this criteria formula in all the cells in the row above the headers, and the range called includes the header and the top cell in the filtered data. The second parameter is the number of the column in the filtered data. (I know, the Ozgrid example makes this redundant)

    Then I Conditional Format the criteria cells to be BOLD and BLUE if they do not = "-ALL-" and the filtered columns now stand out in the same way as the filtered rows!

    With a bit of wrap text and row auto fit the criteria row height is always as small as possible, and it all fits nicely in a table format without all the white space.

    This has worked fine for some years on Excel 97, 2000, 2002 and 2003 so should be fairly robust.

    Good luck

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!