CODEWITHSUNDEEP
vbaexcel
Atlantikdiver
Atlantikdiver

Reputation: 152

VBA: Autofilter with dropdown on multiple entrie cell

I want to filter on Column which includes cells with multiple input e.g.:

Column: B

B2: Banana / Apple
B3: Banana
B4: Apple / Cherry
B5: Banana/ Cherry / Apple
B6: Cherry / Banana / Coconut

By using my macro on Filtering "Banana" from a dropdown, only B3 will be shown, but i want to have all the others as well wehere Banana is listed: B2, B3, etc.

My code looks like this:

Sub Choose_Fruit()
'
'
   Dim Kriterium As String 
   Range("F20").Select 
   Kriterium = ActiveCell.Value 

    '
   Sheets("Fruits").Select
   ActiveSheet.ListObjects("Table9").Range.AutoFilter Field:=1, Criteria1:=Kriterium
End Sub

Any Idea what I did wrong ?

Upvotes: 1

Views: 240

Answers (2)

Vityata
Vityata

Reputation: 43585

Having an * as a wildcard is the way to go, indeed. However, you can write it either in the Kriterium itself, or pass it in the Criteteria1 string, making it a bit more robust.

The biggest problem with your code is that you are working too much with Select, ActiveCell, Activate, etc. This is how to avoid these:

Sub ChooseFruit()

   Dim Kriterium As String
   Kriterium = Worksheets("SomeSheet").Range("F20")
   WorkSheets("Fruits").ListObjects("Table9").Range.AutoFilter Field:=1, _
                            Criteria1:="*" & Kriterium & "*"

End Sub

Upvotes: 2

Siddharth Rout
Siddharth Rout

Reputation: 149305

Use * as wildcard character to search for "Banana" in every cell.

Change

Kriterium = ActiveCell.Value

to

Kriterium = "*" & ActiveCell.Value & "*"

Upvotes: 1

Related Questions