We all are very well aware of the efficiency of MS Excel. However, to further enhance its efficiency, VBA macros come in handy. Without using macros, you can easily filter out information in a dataset, based on criteria, using the ‘Filter’ feature within the ‘Data’ ribbon.

Filtering out the necessary information in a small dataset will require you to use the ‘Filter’ feature. However, if you are looking to filter an immense amount of data based on multiple criteria and conditions, then the VBA AutoFilter functionality will make your life easier. It saves a considerable amount of time and effort by eradicating manual effort.

For example, let’s say you are looking to filter some information within a dropdown list (based on multiple criteria) and to later copy/paste the results into another worksheet. If done manually, this seems like a very time-consuming task. In this kind of situation, VBA AutoFilter will work best for you. The AutoFilter option will simplify the filter process through automation.

VBA AutoFilter Syntax

Expression.autofilter (_Field_ , _Criteria1_ , _Operator_ , _Criteria2_ , _VisibleDropDown_)

Expression: this is the range on which the filter has to be applied.

Field: it is an optional argument, and it represents the column on which the filter needs to be applied.

Criteria 1: this is again an optional argument, and it represents the criteria to filter the data.

Operator: this is required when you are using the second criterion as it helps you to combine two criteria while filtering your data. Some of the operator codes that are used to combine two criteria are:

xlFilterIcon, xlFilterValues, xlFilterDynamic, xlFilterFontColor, xlTop10Percent, xlBottom10Percent, xlBottom10Items, xlTop10Items, xlFilterCellColor, xlAnd, xlOr.

Criteria 2: this is an optional argument and it signifies the second criteria upon which you wish to filter the dataset.

Visible Drop Down: This again is an optional argument and it is used if you need to add the icon of the created filter in the filtered column. The values are only either TRUE or FALSE.

Apart from the ‘expression’, all other arguments are optional. If you do not wish to add an argument, it will add or remove the column’s filter icons.

Let’s look at the code below to get a better understanding of the syntax:

This code will only implement the Autofilter functionality on the columns, andin case this functionality is already applied, the code will remove them.

In simpler words, if you write and execute this code, and there are no filter icons, then the filter icons will appear. If you have a filtered dataset, the AutoFilter method will remove the filters, and you will tend to see the entire dataset.

Example 1: Filtering Data Based on a Text Condition

Let’s talk about the dataset given in the picture above. Suppose we wish to filter the above dataset based on the ‘Product’ column. To implement this filter, we will write the code in the following way:

This code will filter the dataset to display rows that contain the value ‘TV’ under the Product column. The “Sheet1” refers to the worksheet, cell “A1” refers to the cell, and Field :=2 refers to the field on which the filter is to be implemented.

Note that rather than writing this code, the inbuilt filter feature within the Data ribbon can easily handle this functionality.

Now let us look at another example, which is a little more complicated and requires VBA AutoFilter.

Example 2: Multiple Criteria (AND/OR) in the Same Column

We have the same dataset, and we need to filter out the rows where the item is either ‘Laptop’ or ‘TV.’

The code to carry out the above criteria is below:

Here, the XlOR operator is used, which can also be replaced by the AND operator as well. This code will filter rows that have either items ‘TV’ or ‘Laptop’ in it.

Now we can use the same code with a little alteration to filter the dataset where the quantity is more than 10 but less than 20.

Example 3: Multiple Criteria With Different Columns

AutoFilter functionality also helps users in filtering multiple columns at the same time.

For example, if you want to filter the dataset based on the item ‘Printer’ and Person ‘Mark’

The following code will help us create a filter for this criteria:

Example 4: Using AutoFilter To Filter Top/Bottom N Records

Let’s say we want to display the top 10 records based on the Quantity column. The code given below extracts the dataset on the top 10 rows based on the column Quantity:

In this example, ActiveSheet functionality is used. However, this can simply be replaced with the name of Worksheet on which you want to implement the top filter functionality. In this example, if you need to get the top 2 items, you will only have to change, Criteria1:=”5″ to Criteria1:=”2″.

Hence, our above code will be modified to look like this:

You may find it odd, but the operator will always remain ‘xlTop10Items’.

Now to filter out the bottom 5 items, please use the  following code:

Example 5: Using Wildcard Characters in AutoFilter

Let us use the dataset given below:

Now we wish to filter the items which have the word ‘board’ in them. The code to handle this scenario is as follows:

To execute VBA’s wildcard functionality, use *( asterisk) character before and after the word. The wildcard functionality will filter out the rows that have the word  “Board” in it.

Conclusion

VBA AutoFilter is a potent tool. As your dataset grows, the code adjusts itself dynamically. You can move information between sheets with minimum fuss. Excel’s ‘Filter’ tool is an excellent tool in itself; however, VBA AutoFilter is a whole new level of filtering your dataset to get the desired results for reporting. Iterating through a 5,000 row dataset is much more doable with VBA AutoFilter and will save you an immense amount of time.

For more examples, check out this article.