Help creating pull down list with unique names and no blanks

[smiley]

Hi all

I am a newbie and have only basic knowledge of VBA. I have read some tips
from this NG and Pearson's site about creating dynamic range and creating
unique lists. So I have a dynamic range called "Customer" with names in
A10:A25. I have another range called "CustomerUnique" in A30:A45, which is a
list of unique entries in "Customer". This is arrived by using the following
array formula and copying it to fill the range

{=IF(COUNTIF($A$10:A10,A10)=1,A10,"")}

I use the "CustomerUnique" as the list for validation purpose. So cell A5
has a pull down menu for data validation which allow the user to select a
name from "CustomerUnique" and the user is presented only the unique names
to chose. Now this work ok except that I want this pull down menu to give
the following:

1. Unique names only with no duplicates (this part is achieved)
2. Names to be sorted alphabetically (At present cannot sort
"CustomerUnique" using VBA or Excel's sort feature)
3. Avoid blanks from the pull down menu (Currently there are blanks in
between at bottom of list, if at least some entries are same and or when
range is not fully filled up)

The purpose is to do what Autofilter does in Excel, that is allowing to
chose a name and show rows with only with that customers name in the range
"Customer". I am going the VBA way because I want to have many such filters
in the same worksheet, which is not possible if I use the default autofilter
option. I have some basic idea how to do the filtering part with VBA, but
need guidance on creating a pull down list with 3 points mentioned above.

Any help will be appreciated much.

Thanks in advance.

 

[Bob Flanagan]

Smiley, one approach is to copy the column containing the unique entries to
a hidden worksheet and paste special values. Then you can sort the list.
You can get the code for these two tasks by using the macro recorder.

Regarding filling a dropdown list if the sorted list is on a sheet named
"hidden sheet" and in column A, then the following would add the names to
the combobox

For Each cell In Worksheets("hidden sheet").Columns(1)
If IsEmpty(cell) Then Exit For
UserForm1.ComboBox1.AddItem cell.Value
Next

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

 

[Tom Ogilvy]

If you want to use a third range, Chip Pearson shows how to use formulas to
evaluate your unique list and build a list with no blanks:

http://www.cpearson.com/excel/noblanks.htm

This could be done without having the intermediate list I would think - but
the formula would be complex.