How to Filter the Data as You Type in Excel VBA

This post explains how to filter the data as you type with the help of Excel VBA.

Suppose that you have a dataset and you want to apply a filter in Excel with the help of VBA. So that you can filter the name and get them to filter the below dataset.

Here in this post, we are using the below dataset.

Let’s filter the column “Zone” in this table.

We will create a ‘TextBox’ in the cell “A1”, where we will create the filter and we will get our data filtered in the “Zone” column.

Apply Filter the Data as You Type in Excel VBA

So first of all, we will convert this dataset into a table. It is easy to work in table data with VBA.

To convert data into a table, select all the datasets first.

And press CTRL + T keyboard shortcut to convert data into a table.

And press Ok.

See below that your data is converted into a table now.

Once your data is converted into a table, see there is a ‘Design’ tab is added automatically and you can see at the very left side default table is as ‘Table1’.

Now change the name of your table by editing “Table1” as “NewData” and pressing Enter. Our data table name is now “NewData”.

Now go to the ‘Developer’ tab and then click on the “Insert” drop-down.

Go to the ‘Active X Controls’ control box and click on the TextBox tool to create a textbox in Excel.

A TextBox is inserted into the Excel sheet and you can locate it anyplace till then it is in design mode.

We will use this textbox to apply the filter of our dataset.

Now we need to link this text box with any one of the cells, so here we are going to link this with cell “A1” in our Excel sheet.

For linking this textbox just double click on ‘TextBox’.

And find the “LinkedCell” option in the textBox1 properties window.

Type ‘A1’ in the next cell to link it.

And close this window now.

Now go to TextBox and remove the design mode by clicking on the design tab.

The TextBox will work once we remove the design mode from it.

Select the text box by clicking on it and clicking on the design mode button appearing in the ribbon.

You can write anything on this TextBox once it is removed from the design mode, and you will see the same text appearing on the linked cell “A1” as well.

See the image below:-

See that the text we have entered in ‘TextBox’ is entered automatically in the linked cell ‘A1’ also.

Now go to the design mode again and place this text box in place of cell ‘A1’.

See the image above.

Double click on the textbox again in the same design mode to write the VBA code to apply filters to it.

Once you double click on Textbox you will get the below VBA code window.

See that two lines are already written.

And between these two lines, writes the below VBA code to apply filter the dataset.

Let’s try to understand this VBA code.

ActiveSheet.ListObjects(“NewData”).Range.AutoFilter – this is our table object, named as ‘NewData’

Field:=2 – this is the column number, to which we want to apply the filter.

Criteria1:= [A1] & “*” – This is for criteria or the condition that is going to filter in the dataset. (the value in cell ‘A1’ will be filtered).

Operator:=xlFilterValues – this is another parameter to apply filter.

Private Sub TextBox1_Change()
Application.ScreenUpdating = False

ActiveSheet.ListObjects(“NewData”).Range.AutoFilter Field:=2, _
Criteria1:=[A1] & “*”, Operator:=xlFilterValues

Application.ScreenUpdating = True
End Sub

Once you write this VBA code into the textbox.

Now run this VBA code by pressing ‘F5’ or press on the ‘Run’ button on the VBA ribbon.

Once this code is run, close this VBA window again, then click the Design mode tab again to remove the design mode from the text box.

Now see that your filter is ready now.

Write any letter in the filter box and see that the data is filtered for column ‘Zone’.

See that in the image above, as we start typing in the textbox, the below data get started filtered.

So this is one of the amazing ways to apply the filter in the Excel dataset.

If you want to learn more about filters in Excel VBA, please visit Microsoft Office Support.

I hope you find this tutorial useful.

Please feel free to put your comments or suggestion in the comment box below.

How to Filter the Data as You Type in Excel VBA

Apply Filter the Data as You Type in Excel VBA

1 thought on “How to Filter the Data as You Type in Excel VBA”

Leave a Comment Cancel Reply