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