How to Filter in Excel With VBA?

Filter Reports in Excel Using VBA

Most if not all Fortune 500 companies rely on Excel for storing data and analyzing it. Excel has the ability to create reports and also manipulate data due to the high amount of calculations available. Common calculations that are used are the Sumifs, Vlookups, and If statements. There is the next level of excel that makes it a whole lot more productive and that is VBA.

Most reporting you currently is out of excel and creating reports requires formatting, sorting, and filtering. In fact, filtering data in excel is one of the most widely used functionalities. Imagine if you had a report that you compiled every month, but it had to be filtered in a certain way before it was sent out. Now, what if you had multiple columns? There are cases where we are required to perform filtering on specific dates, amounts, and categories. This could take from a few minutes or up to an hour depending on how much filtering needed to be done.

What’s the Solution?

Filtering can easily be done through VBA. You are essentially writing a macro that will run the same lines of code over and over again.

1. First, insert a new module in VBA.
2. Disable filtering if it is active. This can be done easily using “ActiveSheet.AutoFilterMode = False“. This will turn off any filtering that is already done to the sheet. ActiveSheet will filter on the current sheet that is active and being used. If you’d rather specify a specific sheet to be unfiltered, use a reference such as Worksheets(Sheet1).AutoFilterMode= False.
3. Now you want to start filtering. Depending on which column you want to filter, you want to adjust this line of code:
   ActiveSheet.Range(“A1″).AutoFilter Field:=1, Criteria1:=”Inventory”What this will do is adjust the first column to filter for Inventory. If you wanted to filter on the 10th column, you would have to change Field to 10, rather than 1.

There is flexibility with the filtering. One of the biggest advantages of the AutoFilter function is the ability to add wildcards. If you wanted to search the column for anything that started with “Inventory”, you would include an asterisk, like this: “Inventory*”

You are also able to filter based on cell values. If you want to VBA to filter based on what a cell contains in a sheet.

ActiveSheet.Range(“A1”).AutoFilter Field:=1, Criteria1:=Range(“D10”)

If Cell D10 contained the word “Inventory”, it would filter on that criteria.

There are some cases where you want to filter on multiple criteria in the same column. To do this, you will need to utilize the “xlFilterValues” operator.

ActiveSheet.Range(“A1”).AutoFilter Field:=1, Operator:=xlFilterValues, Criteria1:=Array(“Inventory”,”Expense”,”Revenue”,”Asset”)

Arrays can hold multiple values. A list is a good example of this. Instead of repeating the autofilter function multiple times, this can be an easy way of filtering multiple strings or values for the same column.

There is much more you can do with filtering in VBA. If you are running into issues with VBA code, just comment below and the Digital Finance Learning team will assist as soon as possible.