Auto Filters

The AutoFilter feature was added to Excel because people found Advanced Filters too hard. They are cool when used in the Excel user interface. I rarely have an occasion to use them in Excel VBA.

However, one cool feature is available only in Excel VBA. When you AutoFilter a list in the Excel user interface, every column in the dataset gets a field drop-down in the heading row. Sometimes, you have a field that doesn't make a lot of sense to AutoFilter. For example, in our current dataset, you might want to provide AutoFilter drop-downs for Region, Product,

Customer, but not the numeric or date fields. After setting up the AutoFilter, you need one line of code to turn off each drop-down that you do not want to appear. The following code turns off the drop-downs for Columns C, E, F, G, H:

Sub AutoFilterCustom()

Range( Range( Range( Range( Range( End Sub

).AutoFilter Field

).AutoFilter Field

).AutoFilter Field

).AutoFilter Field

).AutoFilter Field

=3, VisibleDropDown:=False =5, VisibleDropDown:=False =6, VisibleDropDown:=False =7, VisibleDropDown:=False =8, VisibleDropDown:=False

I think using this tool is a fairly rare treat. Most of the time, Excel VBA lets us do things that are possible in the user interface (although it lets us do them very rapidly). This VisibleDropDown parameter actually allows us to do something in VBA that is generally not available in the Excel user interface. Your knowledgeable clients will be scratching their heads trying to figure out how you set up the cool AutoFilter with only a few filterable columns (see Figure 11.21).

Figure 11.21

Using VBA,you can set up an AutoFilter where only certain columns have the AutoFilter drop-down.There is no way to selectively hide the AutoFilter dropdown by column in the Excel user interface.

0 0

Post a comment