Range.AutoFilter Method(1) - expression.AutoFilter

1. Syntax

The syntax of the AutoFilter method used in VBA is as follows.
 
expression.AutoFilter (Field, Criteria1, Operator, Criteria2, SubField, VisibleDropDown)
 
ㅇ expression : An expression that returns a Range object, such as ActiveCell, Selection, Range("A1"), etc.
 
ㅇ Field: The offset (relative position) order (index) of the area where the filter is placed. The leftmost part of the list is 1. So, if the list starts at column A, column A is 1, but if the list starts at column B, column B is 1.
 
ㅇ Criteria 1 : Condition 1
 
ㅇ Operator : Operator related to the condition applied to the filter

name	value	explanation
xlAnd	1	Logical AND of Criteria1 and Criteria2
xlBottom10Items	4	Lowest-valued items displayed (number of items specified in Criteria1)
xlBottom10Percent	6	Lowest-valued items displayed (percentage specified in Criteria1)
xlFilterCellColor	8	Color of the cell
xlFilterDynamic	11	Dynamic filter
xlFilterFontColor	9	Color of the font
xlFilterIcon	10	Filter icon
xlFilterValues	7	Filter values
xlOr	2	Logical OR of Criteria1 or Criteria2
xlTop10Items	3	Highest-valued items displayed (number of items specified in Criteria1)
xlTop10Percent	5	Highest-valued items displayed (percentage specified in Criteria1)

 
ㅇ Criteria2 : The second criteria (a string). Used with Criteria1 and Operator to construct compound criteria. Also used as single criteria on date fields filtering by date, month or year. Followed by an Array detailing the filtering Array(Level, Date). Where Level is 0-2 (year,month,date) and Date is one valid Date inside the filtering period. 
 
ㅇ SubField : The field from a data type on which to apply the criteria (for example, the "Population" field from Geography or "Volume" field from Stocks). Omitting this value targets the "(Display Value)".
 
 - Click File > Blank workbook(Ctrl + N) and Type Seoul in cell A1, then click the Data tab > Data Types group > Geography

 
A Geography icon is displayed on the left side of Seoul, and an insert data button is created on the right side.

 
Click the Insert Data button and click Population

 
 
The population is added to the right cell B1.

ㅇ VisibleDropDown: True to display the AutoFilter drop-down arrow(▼) for the filtered field. False to hide the AutoFilter drop-down arrow for the filtered field. True by default.
 

2. Usage

Let's practice with the file below.

water_quality.xlsx

0.01MB

 
The data is in the form of string, number, and date as shown below (Data should not be trusted).

 
First, click Development Tools - Visual Basic to enter the Visual Basic Editor,

 
Then select 'water_quality.xlsx' and insert the module.

 

3. expression.AutoFilter

(1) expression
① expression returns a range object. Since the range with data must be returned, the cursor must not be placed in a part without data. 
Place your cursor in cell K1 and click on Filter under Sort & Filter in the Home tab > Edit group and it will tell you to 'select a single cell in a range' as shown below.

 
② Expressiont can be ActiveCell or Selection of the area where data is located. However, heading can be selected more than two cells.
 
Copy Below codes,

Sub autofilter_expression1()
    ActiveCell.AutoFilter
End Sub

Sub autofilter_expression2()
    Range("b2").Select
    Selection.AutoFilter
End Sub

Sub autofilter_expression3()
    Range("b2:c2").Select
    Selection.AutoFilter
End Sub

Sub autofilter_expression4()
    Range("b1:c1").Select
    Selection.AutoFilter
End Sub

Sub autofilter_expression5()
    Rows(1).AutoFilter
End Sub

Sub autofilter_expression6()
    Columns(1).AutoFilter
End Sub

 
and paste in visual Basic Editor.

 
③ Click 'View Microsoft Excel'.

 
④ Place the cursor anywhere in the data area and click 'Developer > Macros' and select 'autofilter_expression1'. And then click Run button to set the filter in the heading. 

 
If the filter is not set, running macro autofilter_expression1 apply the autofilter method and if the filter is enabled, running macro autofilter_expression1 make the autofilter method removed(toggle function). 

 
⑤ The autofilter_expression2 macro moves the cursor to cell B2, and sets a filter on the part corresponding to the title cells based on the current area, which is a adjacent range based on cell b2.

 
⑥ However, autofilter_expression3 is not what we want because the filter is set in cells B3 to C3, not in the title cell.

 
⑦ However, autofilter_expression4 has limitations set on some of the title cells, but there is no problem if it targets only those.

 
⑧ When autofilter_expression5 is executed, the filter is applied to row 1. 

 
⑨ autofilter_expression6
Only the first Column A is set to filter District.