Tom’s Tutorials For Excel: Showing or Hiding Specific AutoFilter DropDowns

Tom’s Tutorials For Excel: Showing or Hiding Specific AutoFilter DropDowns

You might want to AutoFilter a range of data, but only have the AutoFilter drop-downs be visible in the header row for some columns and not for others. This is not possible to accomplish manually, but it is possible with VBA.

Suppose you have a table of data with fields for text entries, and other fields for numeric data, and you only want to filter for the text-containing columns.

Default view of a table range being AutoFiltered.
All cells in header row 1 show the AutoFilter field dropdowns.




Custom view of a range being AutoFiltered, with only the cells in header row 1 that you specify
for showing the AutoFilter field dropdowns (in this example, excluding cells C1 and D1).




Here is an example macro that shows how to accomplish this. Notice that the VisibleDropDowns are set to False for fields 3 and 4 in the code, which correspond to columns C and D on the worksheet when the table’s first field is column A.

Sub AutoFilterCustom()
With Range("A1").CurrentRegion
.AutoFilter Field:=3, VisibleDropDown:=False
.AutoFilter Field:=4, VisibleDropDown:=False
End With
End Sub
Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,
10 comments on “Tom’s Tutorials For Excel: Showing or Hiding Specific AutoFilter DropDowns
  1. Mark Blackburn says:

    That is a very helpfully tip! Thanks Tom.

  2. RAJNEESH says:

    very helpful!

    Btw what what is CurrentRegion here?

  3. John Gallagher says:

    Hi Tom
    I’m working through the Excel VBA 24-Hour Trainer book, and after reaching this chapter I immediately found a use for this code to make my existing spreadsheet filters more user friendly.

    Having examples that can be used IRL makes learning so much more interesting.
    Many thanks
    John

    • Tom Urtis says:

      Hi John, thank you for buying the book and following my Excel blog. Always great to hear back from readers’ experiences of what works for them, and what solutions I propose that can be improved.

  4. Paul says:

    HI JOhn, just starting to work through the book and a little confused by the macro. I see the with, but at what part is the auto filter command? that actually puts the filter on. For instance if you took out lines 3 and 4. then what would happen?

    • Tom Urtis says:

      Thank you for buying the book. By the way, my name is Tom, not JOhn.

      You can easily answer your own question by placing an apostrophe character in front of each of those 2 lines to see that the drop-down arrows would be visible for those 2 fields. But if you do comment out both those lines, no autofilter would be applied, so what you would do in actual practice is to delete the entire With structure and simply use the one-liner

      Range(“A1”).CurrentRegion.AutoFilter

      • Paul says:

        Got it. But why is autofilter used twice for each filed and why is it not

        Sub AutoFilterCustom()
        With Range(“A1”).CurrentRegion
        .autofilter
        .AutoFilter Field:=3, VisibleDropDown:=False
        .AutoFilter Field:=4, VisibleDropDown:=False
        End With
        End Sub

        essentially why is there no autofilter specific for the A1 line as it seems the other two relate to a certain column

        • Tom Urtis says:

          There is an inferred inclusion of A1 (and B1:E1) by virtue of the Range(“A1”).CurrentRegion statement. The original code I wrote singles out two fields (3 and 4 which in this case is column C and column D) that shall have their drop-down arrows be invisible.

Leave a Reply to Mark Blackburn Cancel reply

Your email address will not be published. Required fields are marked *

*