AUTOMATIC SEARCH FILTERS IN EXCEL
In this EXCEL MAGO video, we show how to turn raw data into dynamic information that you can filter while you type. We recommend you to watch this video first, and check this article for more detailed information:
Now, let’s take each step at a time:
Step 1: Cleaning your data
First, it’s very important to clean up your raw data since the very first step.
If there are additional rows, additional columns and/or text that you don’t need, you can clean it up at a starting point.
To ensure that we have the right file format, we can also choose a name for our EXCEL and save it as MACRO-ENABLED WORKBOOK. This step will be critical if we want, later on, to have our VBA codes up and running.
Step 2: Creating a dynamic table
What is a dynamic table?
Dynamic Tables in Excel are the tables where we add, or update, new values in an existing dataset. As a result, the table readjusts its size, changes the ranges into named ranges, autopopulates formulas based on the first row, refreshes or modifies the linked generated reports and Pivot Tables with the changes in the dataset.
How to create a dynamic table?
To create a dynamic table you can either select the entire range that you want to turn into a dynamic table and go to HOME>STYLES>FORMAT AS TABLE:
Or, simply select your range and press on CTRL+T (Windows) or UP+T (MAC) to create your table.
Dynamic Table name
Once you have the dynamic table created, you can click anywhere within it, and then you’ll notice that a new menu option will be available at your EXCEL level. The new option is TABLE DESIGN and, among others, you’ll be able to check your table name or change the table name into the one that you prefer.
By default, EXCEL will name each table as Table1, Table2, Table3, and so on.
Step 3: Activating the DEVELOPER MODE
CUSTOMIZE RIBBON
As soon as you are all set with your dynamic table, you can go to the next step, which is to activate the DEVELOPER MODE (unless you have previously activated it). And, there are 2 ways to do that.
The Simple way: is to right-click on any empty space in any MENU option (Home, Insert, …), and select CUSTOMIZE RIBBON.
(What we like to call) the Complicated way: Go to FILE>OPTIONS>CUSTOMIZE RIBBON.
Add the Developer Mode
Once you are in the CUSTOMIZE RIBBON window, you can simply tick the DEVELOPER option.
Then, you’ll be able to see it added among your MENU options.
Step 4: Developer TextBoxes
Hooray!!! You have your Developer Mode ON, and now you can do much, much more with your EXCEL files.
And, if you have the VIP-AREA password, you can also download lots of VBA CODES for FREE, and fully automate your EXCEL!
Now, let’s get back to our Developer TextBoxes.
What are ActiveX Control TextBoxes
TextBox is used to display data, edit data, etc in the rectangular box. The Textbox can be linked to a Worksheet Cell. The TextBox can have static and dynamic values. When data or text is static in the TextBox field, it represents the read-only information. It can be used on the WorkSheet or UserForm.
Setting up the text boxes
To set up an ActiveX Control TextBox, you can do in DEVELOPER>CONTROLS>INSERT>ACTIVEX CONTROLS>TEXT BOX.
As soon as you set up your ActiveX TextBox, you can create it as you wish. In our case is, again, a TextBox that works with a VBA code.
At this step, you can simply draw your TextBox in EXCEL. You can draw it anywhere in your Excel worksheet that makes sense for you.
TextBox properties
You can draw your TextBox as you wish, in the most relevant spot for you. Once you do this, you can right-click anywhere on the TextBox and select its PROPERTIES:
In our case, we need to pay attention to the TEXTBOX NAME and use either the default name or change it to something that we need to use in our VBA code later on. We also need to change the LINKED CELL value to a value from a cell where we want the data to mirror. For example, we can specify the G4 cell as linked cell.
With this data, we can imagine that we want to add a filter at the first column level, and search by YEAR.
Step 5: The VBA code(s)
Let’s look first at the full code, and then check it part by part:
Option Explicit
Private Sub TextBox1_Change()
Application.ScreenUpdating = False
ActiveSheet.ListObjects("Table3").Range.AutoFilter Field:=1, Criteria1:="*" & [G4] & "*", Operator:=xlFilterValues
Application.ScreenUpdating = True
End Sub
Sub Clear_All_Filters()
Dim lo As ListObject
Set lo = Sheet1.ListObjects(1)
lo.AutoFilter.ShowAllData
Call Clear_TextBox1_Data
End Sub
Sub Clear_TextBox1_Data()
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
If Application.WorksheetFunction.IsText(ws.Range("G4")) = True Then
ws.Range("G4") = ""
Else
End If
End Sub
THE SEARCH FILTER VBA
To add our VBA code to filter data in EXCEL, we need to right-click on the Sheet name and from there to select the VIEW CODE OPTION.
It’s important to keep in mind that this option is available in any EXCEL file format, but in order for the VBA codes to be able to run, we need to have the file saved in a MACRO-ENABLED WORKBOOK.
In the VBA (Visual Basic Application) editor, we can start by adding the first part of the code:
Option Explicit
Private Sub TextBox1_Change()
Application.ScreenUpdating = False
ActiveSheet.ListObjects("Table3").Range.AutoFilter Field:=1, Criteria1:="*" & [G4] & "*", Operator:=xlFilterValues
Application.ScreenUpdating = True
End Sub
What we need to ensure is that:
TextBox1: This should match exactly the name of the TextBox that we used before.
Table3: Is the name of the Dynamic Table that we specified.
Field:1: In the column number, or in which column, we want to search for our data. As we search by Year — which is the first column — we can specify field number 1.
G4: This is the cell that we linked to the TextBox (where we want the data to be mirrored).
Once we have all the details entered correctly, we can save and close the VBA editor and start using our search/filter bar to get the selection that we want.
Clearing the applied filters
As soon as we play with the start playing with the filters, we might feel the need to add another button to clear the previous filters once we are done.
For this purpose, we could add a VBA button (with few or none visual customization option), but we prefer to add a simple shape using INSERT>(ILLUSTRATIONS if present in the menu)>SHAPES>RECTANGLE.
Once we press OK, we can see that our shape is created. We can add a text on it. In our case, we will simply type CLEAR FILTERS on top of the shape.
Then, we can simply return to our VBA Editor by right-clicking on the Sheet name.
There, after the first piece of code, we can add this part:
Sub Clear_All_Filters()
Dim lo As ListObject
Set lo = Sheet1.ListObjects(1)
lo.AutoFilter.ShowAllData
Clear_TextBox1_Data
End Sub
In this piece of code, what we actually state is that we want to clear all filters and deactivate the filters activated by our TEXTBOX.
Again, it’s very important to use the name of the TextBox; so, if your TextBox it’s not called TextBox1, then you need to use the name that you specified in the TEXTBOX PROPERTIES.
Please keep in mind that what we want to achieve is to create a CLEAR ALL FILTERS code that will be triggered by pressing on the SHAPE to clear all the active filters from the TEXTBOX.
Now, we can save the VBA and close the editor, and get back to our SHAPE. We can right-click on it and select ASSIGN MACRO:
Then, we can select our newly added code: Clear_All_Filters
As a final step, we might want to also clear the data in the TEXTBOXES because the clear filters is clearing the active filters, but it’s not clearing the text that we wrote for the search in the TEXTBOXES.
To do this, we can get back to our VBA editor, and add a new piece of code:
Sub Clear_TextBox1_Data()
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
If Application.WorksheetFunction.IsText(ws.Range("G4")) = True Then
ws.Range("G4") = ""
Else
End If
What is important in this code is:
We need to specify the correct Sheet name. In our case, it’s Sheet1.
The linked cell should be the one specified in the TEXTBOX properties. As you remember, we used G4.
As a condition, we can say that if G4 ISTEXT (it has text), we want to replace the text with nothing. In the EXCEL syntax nothing in a cell is represented by ““. If you add a space between the quotes, “ “, it will not work, as it won’t clean all the data, but it will leave one space that you would need to remove manually.
To make this last code work and triggered by the same shape, we need to change a bit the second code and at this line:
Call Clear_TextBox1_Data
The second code should look now like this:
Sub Clear_All_Filters()
Dim lo As ListObject
Set lo = Sheet1.ListObjects(1)
lo.AutoFilter.ShowAllData
Call Clear_TextBox1_Data
End Sub
So, now, when you press on the SHAPE it will trigger the second code, and the second code, before ending (before END SUB), it will trigger (CALL) the following code/the third one that will clear the previous search.
And you’re good to go and add even thousands of lines from where you can simply search by typing what you are looking for.
On EXCEL MAGO you have the video showing step by step what to do. On the VIP-AREA you have both the ready to use EXCEL FILE (FREE FOR DOWNLOAD), and the codes that you can download. Enjoy them all!
