List box data without duplicates

[redeye]

I have a column of data that I would like to add to a list box. How do I add to contents of the column less any duplicates?

 

[Ivan F Moala]

On 2002-09-05 22:26, redeye wrote:
I have a column of data that I would like to add to a list box. How do I add to contents of the column less any duplicates?

One way to do this is to Basically
1) Clear a Hidden sheet say Column A ready
for a sorted list.
2) Use AdvancedFilter to copy the list to Column A of the hidden sheet and remove all dupes.
3) Parse the address of the unique items
4) Set a reference to this new list eg
'// Clear old ListBox RowSource
.RowSource = vbNullString
'// Parse new one
.RowSource = strRowSource





_________________
Kind Regards,<font size=+2><font color="red"> I<font color="blue">van<font color="red"> F M</font color="blue">oala</font><font size=1> From the City of Sails

This message was edited by Ivan F Moala on 2002-09-06 00:49

 

[Andrew Poulsom]

Or follow this link:

http://j-walk.com/ss/excel/tips/tip47.htm

 

[redeye]

Andrew,
Top stuff thats just what I'm looking for.
Thank you!

 

[GeorgeB]

This will do it.

Private Sub UserForm_Initialize()
Dim List As New Collection
Dim rnArea As Range, rnCell As Range
Dim vaValues As Variant

Set rnArea = ActiveSheet.Range(Range("C1"), Range("C65536").End(xlUp))

On Error Resume Next
For Each rnCell In rnArea
List.Add rnCell.Value, CStr(rnCell.Value)
Next rnCell
On Error GoTo 0

For Each vaValues In List
UserForm1.ListBox1.AddItem vaValues
Next

End Sub