VBA to create validation List & Ignore duplicates

**jordan2322** · 04-04-2012, 06:54 PM

Hi guys,

I want to create a drop down style Validation List from the following data set

Col G
row 20: OH
row 21: OH
row 22: OH
row 23: OH
row 24: UG
row 25: UG
row 26: SV

However I do not wish for the duplicate entries to be allowed. ie from the example above the list should have as selectable options: OH, UG, SV

The used range for these list varies - the list should be dynamic such that it will work until the next blank space is found in column G - perhaps using excel down function.

kind regards

**JieJenn** · 04-04-2012, 07:19 PM

Assuming your list is stored in Column A, just enter the code below in the worksheet module.

Please Login or Register  to view this content.

**jordan2322** · 05-28-2012, 12:58 AM

Is there a way we can tell this to ignore specific Values. I.e. Ignore all numerical values found in the range.

Or - could we set it up to only display those values that appear exactly 1 row below cells in column A that have a Top Border.

Regards

**mterhaar** · 06-15-2013, 10:52 AM

Hi There,

The above code and workbook is exactly the solution I have been looking for. I imagine this is a silly questions but I am having trouble getting the data validation list to a seperate worksheet. (always appears in C1)

Any ideas?