VBA Blogs: Multiple Criteria Filter

Welcome back to the VBA blog. This week, we are going to learn how to use VBA to filter a data table based upon multiple criteria.

Today, we want to create a VBA script to automatically filter a data table based upon multiple criteria. It is a useful method to filter a target data table without instigating a repetitive manual operation.

For example, let’s filter the following data table below based upon the region and item:

If we want to filter the data table with the criteria region equal to East and item equal to Binder, the result would look like this:

The first step is to set the ScreenUpdating application to False.  This ScreenUpdating application property in VBA is used to toggle screen updating on or off.  When we set the ScreenUpdating property of an application object to False it will speed up the macro.  It’s an example of what is known as an optimising technique.

Then, we use the With statement to locate the Data worksheet.  If the FilterMode is set to ‘on’, then this will make the data table show all data.  AutoFilter syntax is added to the worksheet at range A1 and the parameter Field is set to two (2), which points to the second column (Region column) in the data table.  The first criterion we want to use for the region column is East.  Similarly, applying the same logic to the fourth column, we will use the criterion for the Item column to be Binder.

The last code is to turn on ScreenUpdating property as shown below.

Combing all the lines of code together, we get this:

By using this method, we can change the criteria in the code and run the code to obtain filtered data table.

See you next week for more VBA tips!