Welcome to Excel Jet Consult Blog Post. In this blog post, we will learn how to perform multiple filtering by writing Exccel VBA code. Let’s get started
We want to filter the data in the caption below. The filter criteria are:
All the Order Date in 2017 from the States of Alabama and Arizona with Sales that is greater than or equals to 500
Note, the range of the data we are working with spans from A1:N9995 which is very important when writing our code
Press ALT + F11 to open the VBA Editor and insert a module
In the code window, execute the lines of codes as seen in the caption below
The first line of code applies filter button to the data set. The second line indicates that we filter field two which is column B for Order Date that start from January 1st to December 30th 2017
The third line of code indicates that we also filtered field eight which is column H (State). Furthermore, we specified the first and second criteria which are Alabama and Arizona respectively.
The final lines of codes indicate that we applied filter command to field 14 which is the column N (Sales) and the only criteria specified is greater than or equal to 500.
Finally, we will close the VBA Editor and insert a shape which we have done as seen in the caption below
We will assign the macro to the shape. To do that, right click on the shape and select Assign Macro
Select the Filter_Macro and Click OK
Finally, click on the Filter button. From the caption below, the data has been filter including the assigned macro button.
Hope you enjoy the tutorial