In this blog, I will try to cover how to use a custom VBA autofilter in excel. This Advanced Excel filter technique is meant for Advanced Excel users. We all know how to use a custom filter or advanced filter in Excel.
Here we will discuss creating custom Excel VBA autofilter:
For example, you have a huge list of employee names with all information like Emp_id, first_name, last_name etc. where you’re interested in creating separate sheets with employee names starting with a different letter. You can find the employee with a name beginning with “A” (and later on B,C,D,…Z). You can easily apply autofilters and then apply Text filter, then you need to choose BEGINS WITH and then you need to type A and then press ok. This will give you the list of employee names in the same list. After that, you need to copy the entire range, paste it in a new sheet. And you need to follow the same process for other letters as well.
But here we will write a small piece of code which will help us to fetch the records from the list and create a new sheet and then paste the data into a new sheet and it will also rename the sheet.
I have the dataset like shown below:
In my case, I have given the name as “Name”. You can give as you like it.
Now you do not need to do anything in your excel sheet. What you need to do is you need to go your Visual Basic page by pressing Alt + F11.
First, you need to create a user form. If you are on VBA page, then go to Insert and then click on a user form. Then create the user form like below.
I have taken only one label and one combo box and given the name of the combo box as cmbname.
And taken one command box and have given the name as cmdok.
Now you need to select your cmbname combo box as you need to connect the range (A, B, C, D…) that you have created earlier. After selecting the combo box, you need to go to row source prosperities and give the same name what you have given the name in your name manager for A, B, C, D…range.
Now if you press F5 to run this form you will get the same value is coming in the combo box.
Click on the cross (x) to go back to the VBA page once again. Now the thing is very clear to all of us. The user will choose the letter from the combo box and then press the OK button in the form and it will create a sheet and paste the data into the sheet. It will only fetch the records only where the first name should start with the letter what value you have chosen in the combo box.
In the user form, double click on the cmdok command button it will open a new page and then paste the below code in the middle.
Here’s the VBA code:
Private Sub cmdok_Click() Dim boxvalue As String boxvalue = Me.cmbname.Value ‘ it will capture the value you have given in the box. Dim sht1 As Worksheet Set sht1 = Worksheets("emp") ‘it is your raw data sheet. Where all the employees’ name is present. ‘It is now checking whether the sheet name is available or not. If the sheet is present, then it will delete the sheet and create a new one. For Each sht In Worksheets Application.DisplayAlerts = False If sht.Name = boxvalue Then sht.Delete End If Next Worksheets.Add(after:=Sheets(2)).Name = boxvalue ‘ inserting a new sheet and give name of the sheet. Dim sht2 As Worksheet Set sht2 = Worksheets(boxvalue) ‘ The below code is now applying filters in your raw sheet and then pasting the records in the new sheet. With sht1 .AutoFilterMode = False .Range("A1:K1").AutoFilter .Range("A1:K1").AutoFilter Field:=2, Criteria1:="" & boxvalue & "*" End With sht1.Select Range("A1").Select Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy sht2.Select ActiveSheet.Paste Application.CutCopyMode = False sht1.Select Selection.AutoFilter Range("A1").Select Unload Me End Sub
Understand the vba autofilter codes:
First, I have declared a variable called box value which will capture the value from the box. Then I have declared variables of the sheet. Then I run a loop through the sheets to check whether the sheet is present or not. If the sheet is present, then it will delete that sheet and create a new one. Otherwise, it will cause an error as you cannot create two sheets at a time with the same name.
Then the code is applying an autofilter to your raw sheet. In my case, the name of the sheet is “emp”. Then applying autofilters on the second field as in my data the first_name is in the 2nd column and the criteria are box value means what value you have chosen.
Then it is pasting the records into the new sheet and then removing a filter from your raw sheet so that next time it runs smoothly.
Now you need to press F5 to run the form and then chose any value from the box and then press the OK button on the user form. I am choosing E and then pressing OK button.
As soon as you will press OK after selecting the letter “E” you will see a new sheet is created and the name of the sheet is given as “E” and all the employees’ first name which starts with “E” is now copied to your “E” sheet and the same is applicable for all the letters.
In the very next blog, we will discuss on multiple criteria and multiple reports.
We highly recommend take Online Excel VBA Tutorial with practical example and use cases.
Continue learning VBA, automate work and stun your colleague
Hope you have enjoyed this blog. Please let us know your valuable feedback. If you’re willing to step up your knowledge in Excel and Excel VBA, we’re always here for you.