User form

[wv1]

Hi All
i have created a userform that contains a list box. This list box builds a UNIQUE list of values from a column- it does so by looping though each row in the column and if it finds a new value it adds it to the list. However, how can I get this list box sorted alphabetically?

My code is as follows thus far:

ListBox1.Clear
Dim AllCells As Range, Cell As Range
Dim NoDupes As New Collection

On Error Resume Next


For Each Cell In Range("size")
NoDupes.Add Cell.Value, CStr(Cell.Value)
Next Cell
On Error GoTo 0

For Each Item In NoDupes
ListBox1.AddItem Item
Next Item

Thanks for any help in advance,

 

[xlbo]

Please poast VBA questions in the VBA forum: forum707

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Hi,

Here are several no VBA code solutions.

Use Advanced Filter - UNIQUE records only - copy to another range. That's the range for your listbox

Use MS Query to return a DISTINCT resultset from the table (Sheet) containing your list. Using MS Query to get data from Excel faq68-5829

Use the PivotTable Wizard to get a unique list.

NO CODE!

Skip,

_[red][/red]