Excel VBA AutoFilter

One of the reasons for Excel VBA’s popularity is its capability to filter and analyze data from huge database with the help of a method known as AutoFilter. This method permits a user to filter the particular data according to a set criterion and aids its users to draw out precise reports and research work on data. AutoFilter method can filter data comprising values with text, numbers, Boolean, or date data types. Whenever the user applies the AutoFilters to its Excel sheet worksheet, black drop-down arrows (also called filter switches) pop up at the right of your column headings.

Although, we have a filter option available under the Data ribbon tab. But this option is only useful to deal with a minimal set of data. For bulk, it becomes complex to use this method. Hence, to resolve this problem, VBA AutoFilter was introduced. AutoFiltering technique is faster and more efficient. Before applying AutoFilter in VBA, the user should always keep in mind to turn off any current filters (if present) and remove them completely. The range object can also be used to declare AutoFilter in Excel VBA.

Syntax

AutoFilter (Field, Criteria1, Operator, Criteria2, SubField, VisibleDropDown)

Parameter

Field (optional) - This parameter represents an integer offset of the field on which bases you want to apply the filter. The leftmost field is considered as field one.

Criteria1 (optional) – It specifies the criteria. For e.g., to specify blank cells use “=”, for non-blank cells use “<>” and for no data “><” is used.

Operator (optional) – It Identifies the operator to use to associate two criteria applied by a filter. It can accept the following values:

• xlAnd – Logical ‘AND’ will be applied criterion 1 and criteria 2.
• xlBottom10Items- The lowest value displayed in Criteria1.
• xlBottom10Percent- The lowest-valued percentage displayed in Criteria1.
• xlFilterCellColor- The cell color
• xlFilterDynamic – The dynamic filter
• xlFilterFontColor – The font color
• xlFilterIcon – filter icon
• xlFilterValues – filter values
• xlOr - Logical ‘OR’ will be applied criterion 1 and criteria 2.
• xlTop10Items - The highest value displayed in Criteria1.
• xlTop10Percent - The highest-values percentage displayed in Criteria1.

Criteria2 (optional) - It specifies the criteria. For e.g., to specify blank cells use “=”, for non-blank cells use “<>” and for no data “><” is used.

SubField (optional)- This parameter represents the field from a data type on which the user wishes to apply the criteria. If this parameter is not specified, by default, it targets the "Display Value".

VisibleDropDown (optional) – This parameter accepts a Boolean value. If it is set to Boolean True, it displays the drop-down window for filtering the field else for Boolean False it will hide the AutoFilter drop-down arrow. Bu default, it is set to Boolean True.

Example

We will use the use Excel Sheet data and with the help of AutoFilter method will put on a criterion to fetch only those rows where sales filed is greater than 35.

Code:

Sub AutoFilter_Example()
 With ActiveSheet
     'FilterMode Property determines whether the AutoFilters are filtering data down
     'and FilterMode are in use
     .AutoFilterMode = False
     'specifying the AutoFilter range
     .Range("A2:I21").AutoFilter
     'setting the parameters and applying criteria
     'at filed 5, filtering sales data more than 35 
     .Range("A2:I21").AutoFilter Field:=5, Criteria1:=35
 End With
 End Sub 

Let’s work with the set-by-step code of lines:

Step 1: Open the VBA developer tab either by using the shortcut keywords Alt +F11 or click on developer window -> visual basic editor.

Step 2: Visual Basic Editor will open. Next step is to create a module. Right-clicking on the VBA Project-> Click on Insert-> Click on Module.

Step 3: In the VBA Module window, within the sub-block, introduce your macro name.

Step 4: Next, we will use the ‘With’ block we will define a series of ActiveSheet.

Step 5: Firstly, we will disable the AutoFilterMode to turn off any current filters (if present) and remove them completely. This property determines whether the AutoFilters are filtering data, or the AutoFilter arrows are visible or not. Although this is an optional method. Nut its always advisable to make sure that the previous filters are put off.

Step 6:  Now, with the help of Range object we will select the data from our Excel active sheet wherein we wish to apply the auto filter method.

Step 7: We will us the parameters of the AutoFilter method and will define the filed number and criteria to put the filter. Unlike in the below example, we have put field=5 and the sales criteria should be greater than 35.

Output

Step 8: Execute the above code either by pressing the F5 shortcut key or by clicking on the Run button.

Step 9: You will notice that your sheet has been filtered and only the row whole sales filed is greater than is displayed. Thus, hiding all the other values. You can copy these values and can paste it at another sheet for your future reference.