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, blu
re
blue, gree

I want my drop down list to read

re
blu
gree

instead of

red, blue, green
red, blu
re
blue, green (like normal autofilter dropdown choices

If I chose red from the drop down list, the filter would then search every cell in that column containing "red" . The list of "red" "blue" and "green" will be maintained somewhere on the spreadsheet. Thanks for any help to program this with VBA
-Ron

You can't customize the way the drop-down shows values, it must show a list
of unique values. However, you can choose "Custom" from the drop-down. From
there you can choose from a list of comparisons to filter with. For example
choose "Contains" and enter "red".
Record macro as you do it once to see how the comparison is written.

Mike F
"ron_m" wrote in message
...
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 drop down list to read:

red
blue
green

instead of:

red, blue, green
red, blue
red
blue, green (like normal autofilter dropdown choices)

If I chose red from the drop down list, the filter would then search every
cell in that column containing "red" . The list of "red" "blue" and "green"
will be maintained somewhere on the spreadsheet. Thanks for any help to
program this with VBA.
-Ron

Thanks Mike. Is it possible to build a filter functionality (similar to the Autofilter) from "scratch" and then populate the dropdown criteria as you wish?