Range.AutoFilter Method(2) - Field, Criteria, Operator, SubField, VisibleDropDown

water_quality2.xlsm

0.02MB

 

 

 

(2) expression.AutoFilter(field, criteria1)

In expression.AutoFilter method, a Field value should be inputted after :=(colon and equals) after Field like Field:=2 without parentheses

 

 

Add below macro to existing macro.

Sub autofilter_field1()
    ActiveCell.AutoFilter Field = 2
End Sub

 

Module1 is shown like below.

 

If you run autofilter_field1 sub procedure, it occurs errors that 'Variable is not defined'.

 

Also, if you do not specify Criteria1, the result is the same as activecell.autofilter although there is field. There is no filtering.

 

So you must specify criteria1 like with :="Garak2-dong"

.

Sub autofilter_field1()
    ActiveCell.AutoFilter field:=2, Criteria1:= "Garak2-dong" 
End Sub

 

If you run autofilter_field1 macro, Village is filtered by Village name which is Garak2-dong.

In the right of B1 cell, filter and drop-down arrow is displayed

 

As mentioned in part 1 (https://lsw3210.tistory.com/entry/RangeAutoFilter-Method1-expressionAutoFilter), even if you insert an empty column to the left of column A, the field order is never changing.

 

While there is no filter and if you run autofilter_expression6 macro, only column A is autofiltered. After that if you run the autofilter_field1 macro ,

 

A 'Run-time Error : AutoFilter method of Range class failed' is occurring as shown below. 

 

So you should click End button, and by clicking Home > Sort & Filter > Filter or Clear you must remove Filter.

 

(3) expression.AutoFilter(field, criteria1, operator, criteria2)

There are xlAnd and xlOr operators that connect two criterias.

Sub autofilter_field2()
    ActiveCell.AutoFilter Field:=2, Criteria1:= "Garak1-dong" , Operator:=xlOr, Criteria2:= "Garak2-dong" 
End Sub

 

If you run the macro above, only the data which village name is Garak1-dong or Garak2-dong will be displayed.

 

(4)  expression.AutoFilter(field, criteria1, operator)

Operators (excluding xlFilterValues) such as xlTop10Items and xlFilterCellColor have only one condition applied, so the condition must be specified as Criteria1. Even if you specify Criteria2, it is not applied.

If you write and execute the code to display the 10 largest numbers based on field 8 as shown below,

Remove Filter before running below macro.

Sub autofilter_field3()
    ActiveCell.AutoFilter Field:= 8 , Criteria1:= "10" , Operator :=xlTop10Items
End  Sub

 

10 data is displayed by descending water-temperature. The peculiar thing is that the data is duplicated. 

 

 

So if you remove the duplicates, there are actually only 5 items(23.5. 23.7, 23.8, 24, 24.4).

 

But modify Criteria1 10 to 14, 15 data is displayed, because the smallest temperature 23.2 is 3.

  

(5)  expression.AutoFilter(field, operator:= xlFilterValues  ​​, criteria2)

When filtering data based on date, use xlFilterValues ​​Operator and only use Criteria2 , not Criteria1. In addition, when defining Criteria2, Array is used, and the first argument specifies the matching part of year, month, and day from 0 to 2, and the second argument specifies the date that is the basis for year, month, and day among actual data. Indicate.

Sub autofilter_field4()
    ActiveCell.AutoFilter Field:= 9 , Operator :=xlFilterValues, Criteria2:=Array( 0 , "2020/07/25" )
End  Sub

 

When I run the above macro after filter - clear, it will show all the data matching the year 2020 because the first argument of the Array is 0. The month and day can be different.

 

 

If I modify the first argument of the Array from 0 to 1 and run the macro

Sub autofilter_field4()
    ActiveCell.AutoFilter Field:= 11 , Operator :=xlFilterValues, Criteria2:=Array( 1 , "2020/07/25" )
 End  Sub

 

Only data for July 2020 is displayed. Day can be different as long as the year and month are the same.

 

(6)  expression.AutoFilter(field, subfield)

First, we need to display the geographic information, select all data in column A, and click Data > 'Convert to Geography'.

 

Then, District name Songpa is changed to Songpa District, and Gangbuk and Gangnam is too.

And the data insertion icon is displayed on the upper right.

 

If you click the Insert data icon, city-related information such as population is displayed. Click Population.

 

Then population is added in the column J, the right of the existing data.

 

In order to setting filter by population tap Filter on the Home tab to turn off the filter, and then tap Filter again.

It's good even if there is no field name like 'population'. 

 

To extract only the data with a population of 500,000 or more, as shown below. Field 1 which has district must be set to Criteria1 and Subfield is set to Population.

Sub autofilter_field5()
    ActiveCell.AutoFilter Field:= 1 , Criteria1:= ">=500000" , SubField:= "Population" 
End Sub

 

Then, only the data with a population of 500,000 or more are displayed, as shown below. 

 

※ In order to delete geographic information, select all data in Column A, right-click, and click Data Type > Convert to Text.

 

(7)  expression.AutoFilter(field, VisibleDropDown)

 

By default VisibleDropDown value is true, So a Drop-Down arrow is displayed in filtered column.

 

 

But if VisibleDropDown is set to False as shown below and run this macro,

Sub autofilter_field6()
    ActiveCell.AutoFilter Field:= 1 , Criteria1:= ">=500000" , SubField:= "Population" , visibledropdown:=False
End Sub

 

Although Filter is applied to column A, there is no drop-down arrow.