Facing problem in finding Syntax for Advanced auto filter with wildcards using Macros

[girishankar09]

I have created an event like when something changes in a cell, then an advanced filter should happen.

The values to be filtered will be in C2:E3 with C2,D2,E2 as headers. the values will be filtered in B6:D67 with B6,C6,D6 as headers.

Now the issue is:

the values which I filter with CriteriaRange are dynamic with values like "BF Delete", "Ship Easy" "English/French", UK,IN which might contain spaces, / etc

The values in the ranger where I apply filter are even more complex and the example are like "BFA BF Delete", DIIP Ship Easy (US & IN).

I code which I wrote are taking exact match and does not work when they have space, commas, forward slash or brackets.

I want it to work it this way and I am unable to find the correct syntax or format I can write the macros for my use case for it to work even when partial words match. My code is mentioned below.


Sub FilterData()
Range("B6:D67").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Range("C2:E3"), Unique:=False

End Sub


I tried the link below and edited the code to below however but it states .Address is invalid.

Sub FilterData()
Range("B6:D67").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Range("C2:E3"), Unique:=False
With Range("C2:E3").Value = Evaluate("""*""&" & .Address & "&""*""")
End With
End Sub

I want the search to be flexible since the search term might be anywhere from between to starting