Speed up an Autofilter with 20k+ rows

561414

New Member
Joined
Aug 25, 2017
Messages
15
Hey everyone. Hope you're all doing well.

I have a listObject table with 20k+ rows that I'm filtering with a textbox.
This is the code I modified a little from extendoffice:

VBA Code:
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
    
    With Application
     .Calculation = xlCalculationManual
     .ScreenUpdating = False
    End With
    
    xName = "tbInsumos"
    xStr = TextBox1.Text
    Set xWS = ActiveSheet
    Set xRg = xWS.ListObjects(xName).Range
    
    If xStr <> "" Then
        xRg.AutoFilter Field:=2, Criteria1:="*" & Replace(xStr, " ", "*") & "*", Operator:=xlFilterValues
    Else
        xRg.AutoFilter Field:=2, Operator:=xlFilterValues
    End If
    
Err01:
    With Application
     .Calculation = xlCalculationAutomatic
     .ScreenUpdating = True
    End With

End Sub

Issue 1:
It works, but it takes a few miliseconds to display both the result and the letters being written in the textbox, which makes it feel slow.

Issue 2:
I'd like it to ignore the word order, for example: If in my table there's an item "BROWN MARBLE", I'd like to be able to type, say, "marb bro" and still get the result. I had partial success by simply replacing the spaces from the filter criteria with a wildcard (*) but it doesn't ignore the word order.

I've been helped to accomplish something similar here, but this time I'm filtering a table, instead of a listbox.

Here's a sample file with dummy data:

How can I make it faster and ignore the word order from the filter criteria string?

Thank you all very much for your help.
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Forum statistics

Threads
1,223,227
Messages
6,170,849
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top