Microsoft Office Tutorials and References
In Depth Information
ADD FILTER TO SELECTION FUNCTIONALITY
Excel hides all the rows that do not contain General Motors in column F
Figure 139. You can i lter the data set to show only General Motors records.
Note that the macro is additive: After ﬁ ltering by customer, you can ﬁ lter to just
ABC records in column D by selecting D8 and clicking Filter to Selection again.
You end up with just the sales of ABC to General Motors. Choose the word
Central in C8 and click Filter to Selection. You now have just the Central
region sales of ABC to General Motors.
To return to all records, you can run the AutoFilterToggle macro or simply
turn off the Filter feature. In Excel 2007, you click the large Filter icon on the Data
tab. In Excel 2003, you select Data, Filter, Show All or Data, Filter, AutoFilter.
How the Code Works
The heart of the code is the line with the AutoFilter method. In this case,
the AutoFilter method is applied to the Selection. You are taking advantage
of the fact that applying AutoFilter to a single cell automatically applies the
ﬁ lter to the current region. Two named parameters control AutoFilter in
this macro. The ﬁ rst parameter is the Field parameter. This is an integer that
identiﬁ es the column number. In Figure 138, notice that columns A and B are
blank. Thus, the current region is C2:H564. The AutoFilter method numbers
columns starting with 1 as the leftmost column in the data set. Because column
C is the ﬁ rst column in the data set, you specify Field:=1 to ﬁ lter based on
To make the macro more general, you ﬁ lter to the ﬁ eld number of the active
cell. This is stored in a variable called ColNum . You’ll see how ColNum is
The second parameter for the AutoFilter method is the Criteria1 parameter.
To ﬁ lter the data set to only Exxon customer records, you might use: