Using the Advanced Filter with VBA
The advanced Filter provides users with the opportunity to use more complex criteria than the regular autofilter. When you use Excel's Advanced Filter you can filter the data by criteria inside your table based on multiple criteria and in multiple columns at once. Excel's Advanced Filter will copy filtered data to another location, it will also isolate unique items within a list (column). This ability to capture unique items in a list is particular interest as extracting a unique list with the Advanced Filter is seamless in Excel.
Extracting Data Based on Multiple Criteria in Multiple Columns
The following is an example of adding multiple criteria to an advanced filter to return data to a separate sheet.
The above shows the criteria for the Advanced Filter. The items on the left of screen with the red box around it are the criteria for the database. When the range is returned the headers are returned also. In the example above the Advanced Filter will return all of the items where the Due Date < =7 Jan 20 and the LSR Name = J Smith and where Interest >= 500
You can of course extract this without code but for the purposes of this article the code which accompanies the above procedure is as follows.
Sub AdvFilt() 'Excel VBA example using the Advanced filter.
Dim sh as Worksheet
Set sh = Sheet1
Sh.Range("A1", Sh.Range("M" & Rows.Count).End(xlUp)).AdvancedFilter 2, Sheet2.[a7:c8], [a10]
The 2 above stands for xlFilterCopy I used the index number to save a bit of space as the line was getting long and I don't like to move the data to the next line unless it is absolutely necessary. The Excel file attached shows a working example of the above.
Extracting Unique Data from a List
Extracting a list of unique items is a common request and can be a handy way to get the information required for a summary table, a validation list or to provide analysis. Extracting that list can be done in a number of ways, this article will outline some ways extract a list quickly. The Advanced Filter has the capacity to summarise a list into a grouping of unique items quite quickly. The following is the Excel Method to extract the data followed by the vba required to do the same thing.
On the Ribbon under Data click on Advanced. The following popup will appear.
Action - click on the Copy to another location radio button
List Range is the Range which contains the list. Note this will be a 1 column range.
Criteria Range - not required for this procedure.
Copy to - Choose the cell you want the Unique List.
Ensure the Unique records only tick box is ticked.
Excel should now produce a unique list in Column T.
The following will extract unique records from Column A and put them in Column T. The result is deliberately put away (Column T) from the dataset so as not to overwrite any important information.
The [T1] is the cell the data will be copied to and True at the end is short hand for Unique:=True. The above code is very handy for getting unique data all by itself but if we want to leverage off this excellent tool we have to combine the procedure with others to manipulate every item within the list.
The above can be summarised in vba with the following procedure.
There are other more complex ways to get a unique list. The following does the same as the advanced filter List Unique Items with VBA but I have always preferred the Advanced filter for its simplicity and speed.
Using the Advanced Filter with VBA
The following will explain how to use Excel’s Advanced Filter in VBA to extract data from a table using more complex criteria.
Where the criteria for the Advanced filter is in a separate sheet originally titled Criteria.
Notice how the Criteria is cascading for the country and the Country header is repeated. This is effectively the Or condition using the Advanced filter. The Status is repeated for each criteria line. The Data appears from A1 to D4. So the coding needs to reflect this.
Range("A11", Range("F65536").End(xlUp)).Copy Sheet3.[a2]
Sheet2.ShowAllData
The data is filtered in place in the same way the Autofilter would filter data and then the information is copied to a separate sheet. The above procedure will filter England, Wales and Scotland from the Country Column and only when the Status Column says Qualify.
To exclude all of the above criteria, to achieve the opposite.
For the exclusion of data using the And Operator the items go alongside one another. So the above says Exclude England, Exclude Scotland, Exclude Wales and include all items with a status of Qualify which are not part of the afore mentioned Countries.
The code which achieves this is as follows;
Sub AddNonUK()
Range("A11", Range("F" & Rows.Count).End(xlUp)).Copy Sheet4.[a2]
Sheet2.ShowAllData
The code and criteria in the below file are slightly different however the concept is the same.
