MS Excel: Search As You Type Advanced Filtering
MS Excel: Search as you type is another feature that will keep your sheet more organized and make it easy for you to search for a specific field. We’re going to do some VBA codes to the excel file so as to easily filter the table upon the given search criteria. In our example, we have a table list of 196 countries in one column and their capital cities in the second column. Above the table, we have two cells. The first cell, e.g. A1, we have the exact title of the list (e.g. Country) and the second cell A2, we’ll write the search text.
To do so, we’ll follow the following steps:
- In Excel, we’ll create a table with vertical search criteria.
- Save the excel file as “Excel Macro-Enabled Workbook”.
- Head over Developer > Visual Basic.
- Insert the below code to the related worksheet, e.g. sheet1:
Private Sub Worksheet_Change(ByVal Target As Range)
Call Advanced_Filtering
End Sub
- Create a Module and insert the below code based on the length of your table and the search cells you’ve determined (should be in vertical):
Sub Advanced_Filtering()
Range("$A$5:$B$201").AdvancedFilter _
Action:=xlFilterInPlace, _
CriteriaRange:=Range("$A$1:$A$2")
End Sub
Download an example (Note: It’s a macro-enabled Workbook), you’ll need to enable it upon opening.