Excel VBA - Modifying the Autofilter

[mcnamara_r]

When you select the autofilter, the drop down list is created from the
unique data in that column. Is there any way to customize this list, so
the changes are permanent?

For instance, I might have in one(1) column the following 4 row
entries:

red, blue, green (all are single cell entries)
red, blue
red
blue, green

I want my autofilter drop down
list to read:

red instead of red, blue, green
blue red, blue
green red
blue, green (like
normal)

If I chose red from the drop down list, the filter would then would
search every cell in that column containing "red" . I can maintain the
list of "red" "blue" and "green" somewhere on the spreadsheet

Thanks for any help.

 

[mcnamara_r]

Correction to my original post

I want my drop down list to read

red
blue
green

instead of:

red, blue, green
red, blue
red
blue, green (like normal

 

[Bernie Deitrick]

The only way to do this is to use the Custom... option of the autofilter
drop down, and select "Contains" and enter red in the box to the right.

HTH,
Bernie
MS Excel MVP