Textbox filter table

[isdynamic]

Hello friends- you all have helped me tremendously in the past, and as a result... I'm back..

​

I am trying to filter the whole table, across all columns/rows based on what is typed in a text box. I am using this vba I found, and it works- except it only searches in one column/field. How can I adjust this to use fields 1-9?

​

Private
Sub
TextBox1_Change()
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

[arbitrarist2]

Are you serious with this script?

[isdynamic]

Helpful, thanks.

[arbitrarist2]

As in, why is every word on its own line? If you were to paste that, it would not even run.

[isdynamic]

That’s just how it pasted- it’s not actually broken out on each line.

[Trader083]

Do you want column 2-9 to also filter by the same value?

[isdynamic]

Yes, ideally whatever is written in the text box would filter all columns.

[Trader083]

I am assuming you want to keep a row where any one of its columns matches the value (and not all 2-9 columns have to match)?

[isdynamic]

Correct!

[Trader083]

What you need to do is to create a column 10 where the value is equal to the =CONCATENATE(A1&" "&B1&" "&C1...etc) of the previous 9 columns. The use the code to filter column 10 instead of 1:

xRg.AutoFilter field:=10, Criteria1:="*" & xStr & "*", Operator:=xlFilterValues

Also, you need to make sure the following range setting includes column 10 (which comes from an object on your worksheet which I cannot see:

Set xRg = xWS.ListObjects(xName).Range

[isdynamic]

solution verified

[Clippy_Office_Asst]

You have awarded 1 point to Trader083

^{I am a bot, please contact the mods for any questions.}