Create Advanced Excel Filters using Excel VBA

by | April 7th, 2017

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 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:

where you interested to create separate sheets with employee names starting with different letter while using advanced excel filter using excel VBA
In a separate excel sheet write A, B, C, D…X, Y, Z and give a name of that range.

In Seprate excel sheet write Name A, B, C…..Y, Z - Advanced excel filters using excel VBA

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.

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

I have taken only one label and one combo box and given the name of the combo box as cmbname.

I have taken only one label and one combo box and given the name of the combo box as cmbname - Excel VBA autofilter

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 rowsource prosperities and give the same name what you have given the name in your name manager for A, B, C, D…range.

Free excel macro tutorial  for beginnersAfter selecting the combo box, you need to go to rowsource prosperities and give the same name what you have given 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.

Now if you press F5 to run this form you will get the same value is coming in the combo box - vba autofilter

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 boxvalue 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 boxvalue 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.

Final Step:

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.

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

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.

Now yo can see that all the employee first name which starts - Advanced Excel Filters using Excel VBA

Please download the sample excel vba code file from here and play with it.

In the very next blog, we will discuss on multiple criteria and multiple reports.

We highly recommend take Online [thrive_link color=’blue’ link=’http://yodalearning.com/p/excel-vba-tutorials’ target=’_self’ size=’small’ align=”]Excel VBA Tutorial[/thrive_link] 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.

Tags:
error: Content is protected !!