Create filter views in Excel using VBA and Array formulas
Recently I have refactored my filter code for Excel tables and it really saved a lot of work for me. I would like to share it here
My client who had growing data tables wanted to have an easy way to create his own filters and save them like filter views. As he filters based on many columns, it wasn’t efficient to use the filter code from VBA. Also, advanced filters were not a good option he will not be able to add other criteria.
This example dataset ( named Tb_Customers in Excel ) for customers and the client wanted basically to have two filters to start his analysis from
- Females Over 50 HighSchool
- Males Less than $40K Non-Blue Cards
So, I got this idea to filter the data in Excel first then pass it filtered ids (customer number in this case) to VBA. Let’s try to go through the first filter using the filter formula in excel.
FILTER(Tb_Customers[CLIENTNUM],
(Tb_Customers[Customer_Age] >= 50) *
(Tb_Customers[Gender] = “F”) *
(Tb_Customers[Education_Level] = “High School”)
)
Using this formula we can get the result as a filtered column in Excel. Then to save real estate we can use the Textjoin function to make it easier to pass the code in VBA
Sub filterShtByIds(ByVal IdsTxt)
IdsArr = Split(IdsTxt, “,”)
ActiveSheet.ListObjects(1).Range.AutoFilter Field:=1, Criteria1:=IdsArr, Operator:=xlFilterValues
End Sub
Using this VBA procedure, if the joined text of the IDs passed, VBA will filter the IDs by value.
Sub callerBtn()
buttonRw = ActiveSheet.Shapes(Application.Caller).TopLeftCell.Row
filterIDs = ActiveSheet.Range(“D” & buttonRw).Value
Call filterShtByIds(filterIDs)
End Sub
To make it easier for the user to add a new filter button and formula. I wrote this code to take the cell beside the button as the joined text source
You can download and test the Excel file from here: https://github.com/AhmedAbdelkhalek/Excel-VBA-Tools/blob/master/BankChurners.xlsm
Note: this method will be okay as long as the joined text doesn’t exceed the cell string limit which is 32767
Data source: https://www.kaggle.com/sakshigoyal7/credit-card-customers
