Dynamic Filtering in Excel VBA for multiple columns

Question

I have some interesting issues with the dynamic filtering for multiple columns recently. Also, I have searched all over S/O for solutions but none seems to work:

:Autofilter for multiple columns in excel VBA :VBA for filtering columns :Dynamic filter using excel VBA

Recently, I watch a tutorial of Dynamic Filtering in Excel using VBA (Note: I don't have Microsoft 365, hence Filter functions do not work for me).

What I am trying to achieve is that I just want to use only one text box (Linked at cell C3) to filter out my data (as long as it contains the word typed into textbox) at columns 2,3 and 5 simultaneously when the VBA scripts detects changes in textbox but unfortunately, it does not work. It just filters out all the things leaving the table empty.

Here is the code below

Option Explicit 
'Linkedcells is C3


Private Sub TextBox1_Change()
    With ListObjects("Search").Range
        .AutoFilter Field:=2, Criteria1:="*" & Range("C3") & "*"
        .AutoFilter Field:=3, Criteria1:="*" & Range("C3") & "*"
        .AutoFilter Field:=5, Criteria1:="*" & Range("C3") & "*"
    End With
     End Sub

Show some sample data and expected output. What is in C3 cell? — Harun24hr
– Harun24hr, Commented Sep 2, 2020 at 10:43
Hi Harun24HR, the data looks like this imgur.com/a/UqzKbAe . Actually I have a text box (named TextBox1 which I set it linked to cell C3, and as I type into the textbox, cell C3 will show the exact words I type into the textbox. , it will automatically filter out the data based on values in C3 — Alan Koh W.T
– Alan Koh W.T, Commented Sep 2, 2020 at 14:13
From your screenshot it shows Field:=4 is number data. Here * will not work. What are you typing in textbox and which result do you want? Share you expected output here. — Harun24hr
– Harun24hr, Commented Sep 2, 2020 at 14:49
Hi @Harun24HR , apologies, the field should be Field:=5 (Really apologizes for typo) . What I wanted is for one textbox, it should be filtering at column specified as shown. Note: I highlighted yellow and bold for clarity . See screenshot: imgur.com/a/Nxf3WyF .I want to set autofilter for column PO Number, Supplier and model. As I type K, any columns (PO Number/Supplier/Model) that has letter K inside it can be filtered, and as I type Ka (See label 2) , more unrelated to search will get filtered out. The end result is shown like pics labelled 3 — Alan Koh W.T
– Alan Koh W.T, Commented Sep 2, 2020 at 15:54

Gibus · Accepted Answer · 2020-09-02 10:59:00Z

1

With ListObjects("Search").Range
        .AutoFilter Field:=2, Criteria1:="*" & Range("C3") & "*"
        .AutoFilter Field:=3, Criteria1:="*" & Range("C3") & "*"
        .AutoFilter Field:=4, Criteria1:="*" & Range("C3") & "*"
    End With

"it does not work. It just filters out all the things."

Seems like you're filtering out all the fields via that one value. Unless the values in the row are all the same it will filter everything.

answered Sep 2, 2020 at 10:59

Gibus

3052 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Alan Koh W.T Over a year ago

Hi Gibus, you are right, but I just want to filter the columns that contain part of words that I wanted to search.

Harun24hr · Accepted Answer · 2020-09-02 11:01:32Z

1

When you are filtering multiple column the you need to specify operator. Try below, you may need to change operator for your case.

Dim MyTable As ListObject

Set MyTable = Sheets("Sheet1").ListObjects("Search")
    With MyTable.Range
        .AutoFilter Field:=1, Criteria1:="=*" & Range("C3") & "*", Operator:=xlAnd
        .AutoFilter Field:=2, Criteria1:="=*" & Range("C3") & "*", Operator:=xlAnd
        .AutoFilter Field:=3, Criteria1:="=*" & Range("C3") & "*"
    End With

Set MyTable = Nothing

answered Sep 2, 2020 at 11:01

Harun24hr

38.1k6 gold badges24 silver badges47 bronze badges

1 Comment

Alan Koh W.T Over a year ago

Hi @Harun24HR, thanks for sample code but it works in some way but not I wanted. (Note: I changed XLOperator from xlAnd to xlOr with same results). It only filters out the columns that had the letter in all columns selected.

Collectives™ on Stack Overflow

Dynamic Filtering in Excel VBA for multiple columns

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related