CODEWITHSUNDEEP
excelvba
Alan Koh W.T
Alan Koh W.T

Reputation: 423

Dynamic Filtering in Excel VBA for multiple columns

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

Upvotes: 0

Views: 3421

Answers (2)

Harun24hr
Harun24hr

Reputation: 36850

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

Upvotes: 1

Gibus
Gibus

Reputation: 195

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.

Upvotes: 1

Related Questions