Skip to main content

How to do instant filtered search as you type in a cell in Excel?

This article provides a method for you to search and filter instantly as you type in a cell in Excel.

Instant filtered search as you type with VBA code


Instant filtered search as you type with VBA code

As below screenshot shown, you have a Name list which you want to search and filter instantly as you type in a specific cell. Please do as follows to get it through.

1. Firstly, you need to create a table with the list data, and name the table as “Name”. Please select the whole list, click Insert > Table.

2. In the opening Create Table dialog box, click OK. See screenshot:

3. Click on any cell of the table to activate the Design tab, and then rename the table as Name in the Table Name textbox. See screenshot:

4. Click Developer > Insert > Text Box (ActiveX Control) to insert a text box above the table list as below screenshot shown:

5. Right click the Text Box and click Properties, in the Properties window, type a cell reference into the LinkedCell box.

6. Right click the Text Box and select View Code from the context menu. Then replace the original code with the below VBA code in the Microsoft Visual Basic for Applications window. See screenshot:

VBA code: Instant filtered search as you type in Excel

Private Sub TextBox1_Change()
'Update by Extendoffice 2018/08/22
Dim xStr, xName As String
Dim xWS As Worksheet
Dim xRg As Range
    On Error GoTo Err01
    Application.ScreenUpdating = False
    xName = "Name"
    xStr = TextBox1.Text
    Set xWS = ActiveSheet
    Set xRg = xWS.ListObjects(xName).Range
    If xStr <> "" Then
        xRg.AutoFilter field:=1, Criteria1:="*" & xStr & "*", Operator:=xlFilterValues
    Else
        xRg.AutoFilter field:=1, Operator:=xlFilterValues
    End If
Err01:
Application.ScreenUpdating = True
End Sub

Note: In the code, TextBox1 is the name of the inserted text box, Name is the name of the table list. You can change them based on your needs.

7. Press the Alt + Q keys to close the Microsoft Visual Basic for Applications window.

8. Turn off the Design Mode by clicking Developer > Design Mode.

From now on, the table list will be searched and filtered instantly based on the entered value in the text box. See screenshot:

Best Office Productivity Tools

Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

kte tab 201905


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Comments (3)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
what if the table has 2 or more columns?
This comment was minimized by the moderator on the site
а если в таблице 2 и более столбцов?
This comment was minimized by the moderator on the site
what is the name of the tab you are working on tab
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations