Auto Filter Method

The AutoFilter method has two syntaxes, corresponding to two distinct functions. The syntax:

RangeObject.AutoFilter simply toggles the display of the AutoFilter drop-down arrows for the columns that are involved in the range.

The syntax:

RangeObject.AutoFilter(Field, Criteria1, Operator, Criteria2)

displays the AutoFilter arrows and filters a list using the AutoFilter feature.

The optional Field parameter is the offset (as an integer, counting from the left) of the field on which the filter is based (the leftmost field is field one).

The optional Criteria1 parameter is the criteria (as a string). We can use "=" to find blank fields, or "<>" to find nonblank fields. If this argument is omitted, the criteria is All. If Operator (see the following example) is set to xlTop10Items, then Criteria1 specifies, as an integer, the number of items to display (this number need not be equal to 10).

The Operator parameter can be one of the following XlAutoFilterOperator constants:

Enum XlAutoFilterOperator xlAnd = 1 xlOr = 2

xlTop10Items = 3 xlBottom10Items = 4 xlTop10Percent = 5 xlBottom10Percent = 6 End Enum

If this parameter is set to xlAnd or xlOr, then we must use Criteria1 and Criteria2 to construct the compound criteria.

To illustrate, consider the worksheet shown in Figure 19-5.

Figure 19-5. A worksheet before autofiltering

1 Number

B Year


1996 1994

1997 1990 1994





10 11


The code:

Range("A1:B5").AutoFilter 2, "1997", xlOr, "1998"

will filter the range A1:B5 to show only those rows in the range for either the year 1997 or 1998. Note that it has no effect on the remaining rows of the worksheet. Hence, the result will be the worksheet in Figure 19-5 with rows 3 and 4 missing.

Recall that the AutoFilterMode property of the Worksheet object is True if the AutoFilter dropdown arrows are currently displayed on the worksheet. Note that we can set this property to False to remove the arrows, but we cannot set it to True. (To display the AutoFilter arrows, use the AutoFilter method.)

Recall also that the FilterMode property is True if the worksheet is in filter mode. Thus, for instance, if the AutoFilter arrows are displayed but no filtering has taken place, then AutoFilterMode is True, whereas FilterMode is False. Once filtering is actually performed, then FilterMode is True.

This method changes the width of the columns or the height of the rows (depending upon the type of range) to obtain the best fit for the range's contents. The syntax is:

RangeObject.AutoFit where RangeObject refers to a Range object that consists of either one or more rows or one or more columns (but not both). Otherwise, the method generates an error. If the range consists of columns, then the column width is adjusted. If the range consists of rows, then the row height is adjusted.

This method automatically formats a range using a predefined format. The syntax is:

RangeObject.AutoFormat(Format, Number, Font, Alignment, Border, Pattern, Width)

All parameters of this method are optional. The Format parameter can be one of the following

XlRangeAutoFormat constants:

0 0

Post a comment