Creating A Validation List From Unique Values in A Column (Filtered and unfiltered)

[Ark68]

I am looking for a VBA solution that can analyze a column (A) in a worksheet (F_FUNC) to create a list of unique values. This list of unique values will form the list of a cell validation list. I know I can step through each cell in the column, and compile a unique value list in a column on another worksheet, and define that range of values for the validation list. But I'm wondering if thereis a more efficient method.

On a side not, how would this same process work on a filtered list? For example, if I filtered a single value in column A, how would I create a validation list of the unique values in column B as a result of the filter?

 

[RoryA]

If you are using 365, use the UNIQUE function and create the list in another range.

 

[Ark68]

Thanks RoryA, I'll give it a try. But before I put too much effort into trial and error, would this work if the data is filtered?

I found this option as well, but again, fear it won't work on filtered data. (

)

 

[RoryA]

would this work if the data is filtered

Yes, you can handle that by adding in SUBTOTAL, or adding the same criteria into a FILTER function inside the UNIQUE function.