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
That is a very helpfully tip! Thanks Tom.
Thanks for your comment, Mark!
very helpful!
Btw what what is CurrentRegion here?
CurrentRegion represents the range of data using A1 as a cell connected to it.
Other examples on my blog to demonstrate CurrentRegion’s usefulness in contiguously populated ranges:
http://www.atlaspm.com/toms-tutorials-for-excel/toms-tutorials-for-excel-select-a-current-region/
and
http://www.atlaspm.com/toms-tutorials-for-excel/toms-tips-for-excel-color-the-active-cell-row-or-column/
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
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.
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?
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
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
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.