Excel 2010: VBA Convert incoming text into multi-select drop down list

Question

I am regularly receiving a raw export file and I have written macros to format and manipulate it. I use Excel 2010 and I'm moderately comfortable in VBA.

One of the incoming columns has a list of names, from a known (finite but large) list, separated by semicolons. If you use Autofilter, though, it doesn't treat the entries like true multi-selects.

Question: Can I programmatically tell Excel to see "apple; grape" as a multi-select of both "apple" and "grape"?

Example:

Assume that we know the list only contains the following names: apple cherry grape orange peach

The cell(s) in Column (A) might contain one, two, ...or five of these fruits in any combination, separated by ";".

(A1) = apple (A2) = apple; grape (A3) = apple; peach (A4) = cherry; grape (A5) = cherry; orange

Luckily for me, the incoming data is always exported in alphabetical order (so I won't ever have one cell containing "apple; grape" and another cell containing "grape; apple").

The Problem: AutoFilter assumes that each possible combination is a unique 'select' option. In later steps, I need to combine/filter based on multiple criteria that make it complicated to use an array - because I have too large a list to view all the combination permutations as unique possibilities.

[For example: I am filtering to copy a group of data to a new tab, and sometimes I'm just moving anything with "grape" to a new tab, but sometimes anything that contains "grape" OR "cherry" gets copied into the same new tab.]

There is a distinct possibility that I'm overthinking this and making it harder than it has to be!

Thanks!

Edit: In other words, I don't see how to reasonably loop through Range().Autofilter Field... Criteria1:=() with an exponentially large set of combinations. Make sense?

Answer 1

Use advancedfilter instead of autofilter since it can handle as many criterias as you want.
Here is a working example. Set up your Excel sheet like the first screenshot is showing and run the macro.

_{Before macro}

Sub AdvancedFilterTest()

    'clear old results and old filters
    Range("C:C").Clear
    Range("D:D").Clear

    'criteria header name and data header name needs to be the same
    [C1] = [B1]

    'Split multiple criterias by semicolon and save them as array
    Criterias = Split([A2], ";")

    'write the array to cells since advancedfilter needs a range as criteria
    For i = 0 To UBound(Criterias)
        Cells(i + 2, 3) = Criterias(i)
    Next i

    'Set the cells as a range so advancedfilter can use them
    Set critrange = Range(Cells(1, 3), Cells(UBound(Criterias) + 2, 3))

    'advanced filter will filter and copy your data to a new target range
    Range("B:B").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=critrange, CopyToRange:=Sheets(1).[D1]

End Sub

_{After macro}

I'm sure you will get the idea and how to adapt it.