Microsoft Office Tutorials and References
In Depth Information
ADD FILTER TO SELECTION FUNCTIONALITY
Selection.AutoFilter Field:=4, Criteria1:=”Exxon”
The macro speciﬁ es a Criteria1 of ActiveCell . The ActiveCell
property returns a range object that contains the one cell that is the active cell.
Note that someone might select a rectangular range such as C8:H13. Only one
of these cells is the active cell. It is the cell listed in the name box. Technically,
you should be asking for ActiveCell .Value, but it turns out that the .Value
property is the default property returned from a range, so simply ﬁ ltering to
ActiveCell causes Excel to ﬁ lter to General Motors in Figure 139.
Handling the Unexpected
Most data sets I encounter start in column A. Why would anyone leave columns
A and B blank? If you could guarantee that your data sets would always start in
column A, then it would be easy to identify the Field parameter as:
If you are in column C, then ActiveCell.Column is 3. Simple enough.
But the macro goes an extra step and envisions someone daring to start a data
set in a column other than column A. The logic works sort of like this:
What column is the active cell in? It’s in column F, which is column 6.
Okay. What column is the leftmost column in the data set? It’s in column C,
which is 3.
Hmmm. Okay. Then how many blank columns are to the left of the ﬁ rst
column? Well, that is the column number of column C minus 1 (i.e., 3 – 1, or
2). In most cases, the calculation for the number of blank columns evaluates
to 0. Column A is column number 1, and 1 – 1 is 0.
To translate this logic to VBA, Figure 140 asks many of these questions in the
VBA immediate window.
Figure 140. h
ere are some logical steps in calculating the i eld parameter.