Microsoft Office Tutorials and References
In Depth Information
ADD FILTER TO SELECTION FUNCTIONALITY
The active cell is F7.
Cell F7 is column number 6.
The current region around F7 is C1:H564. To ﬁ nd the current region, Excel
proceeds from the active cell in all directions and stops when it encounters the
edge of the spreadsheet or an edge of the data set. An edge of the data set
requires the cells in the row below the data set to be completely blank.
When you ask for CurrentRegion.Columns , you are referring to six columns.
You might feel compelled to ask for CurrentRegion.Columns(1) .Column
to ﬁ nd out that the data set starts in column 3. However, a shortcut is to ask
for the Column property of CurrentRegion.Columns . The Column property
happens to return the column number of the ﬁ rst column in the range. So,
when you ask for CurrentRegion.Column , you get a 3, which indicates that
the ﬁ rst column of the current region is in column C.
The ﬁ rst line of the macro goes through all this logic to ﬁ gure out that Customer
is the fourth column in the current data set. ActiveCell.Column is 6 . The
number of blank columns to the left of the data set is 2. This is ActiveCell.
CurrentRegion.Column (3) minus 1 . So, the ColNum variable is 6 – 2 ,
or 4 .
In order to handle the unexpected, the macro grows to two lines of code. The
ﬁ rst line calculates the column number within the current data set:
ColNum = ActiveCell.Column - _
(ActiveCell.CurrentRegion.Column - 1)
The second line of code turns on AutoFilter and ﬁ lters the speciﬁ c column
to the value in the current cell:
Using the Second Macro
The second macro needs to turn off AutoFilter . If you use the AutoFilter
method with no parameters, it simply toggles the AutoFilter dropdown on
or off. If a data set is ﬁ ltered and you use Selection.AutoFilter , Excel
turns off AutoFilter and shows all records again. So the second macro is
one line of code:
Tip: After this topic was written, I learned that this functionality is already in
Excel! See the Learn Excel podcast episode 851.
Summary: You can use macros to add the Filter to Selection functionality to