Data Validation list with unique values (drop down menu)

0123ren

New Member
Joined
Jul 16, 2009
Messages
10
I have a column of numbers which contains numerous repeated entries and blank cells. The list is free to expand as people can continually add to the list. The list can only expand to a finite number of rows, say 150.

Is it possible to use a bit of VB code to extract only unique entries and non-blanks from this list and copy it to another area so that this "new_list" can be named and used as data validation for a drop down menu.

I would like it to be automated so the length of the new_list created is recognised by the data validation so that there are not numerous blanks at the bottom of the drop-down menu as may be the case if numerous duplicate entries and blanks are removed.

I would be extremely greatful for any assistance.

PS I have looked at many related threads and although many solutions closely approach the one I require there are no direct matches. I am not a seasoned professional when it comes to excel so if I have failed to explain my situation using the correct terminology then please feel free to ask for further explanation.

Thanks in advance for any assistance offered!!
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

0123ren

New Member
Joined
Jul 16, 2009
Messages
10
I suppose a little example of what I require would be helpful.

A1
280
260
125
260

125
280
320

260
125
320


Drop down menu in a cell on another sheet would display.

125
260
280
320

in other words only the 4 unique numbers without any blanks at the bottom.

Again, thanks in advance, I hope this example is clear enough.
 

BeachRock

New Member
Joined
Mar 3, 2012
Messages
7
What might be useful? Found this post looking for the same answer but there isn't any answer to look at... I know this is an old post but, shouldn't the answer provided to the original poster still be available for others to use too?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,178
What might be useful? Found this post looking for the same answer but there isn't any answer to look at... I know this is an old post but, shouldn't the answer provided to the original poster still be available for others to use too?
Either register with the site in order to get the file or post a small sample here along with the results that you want to obtain.
 

BeachRock

New Member
Joined
Mar 3, 2012
Messages
7
I am registered on the mrexcel forum....

Do you see anything at all in Krishna's first post other than a reference to something that might help? There is no URL or file attachment...
 
Last edited:

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,178
I am registered on the mrexcel forum....

Do you see anything at all in Krishna's first post other than a reference to something that might help? There is no URL or file attachment...
You have also to register with the site of the below link:

This
 

Watch MrExcel Video

Forum statistics

Threads
1,100,194
Messages
5,473,075
Members
406,845
Latest member
JohnR123

This Week's Hot Topics

Top