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:
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.
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:
Dropbox
www.dropbox.com
How can I make it faster and ignore the word order from the filter criteria string?
Thank you all very much for your help.