you an use a collection to get the unique values. a scripting dictionary,
part of the scripting runtime is better as it has an Exists method. With the
collection, trying to add a key that already exists raises an error. so by
adding an item to the collection can be used to parse for uniqueness.

1. Using a collection
Sub GetUniqueItems()
Dim cell As Range
Dim coll As Collection

Set coll = New Collection

On Error Resume Next

For Each cell In Range("MyList").Cells

coll.Add cell.Value, cell.Value
If Err.Number = 0 Then
'ok to add
Sheet1.ComboBox1.AddItem cell.Value
Else
' it already exists, hence raises an error
Err.Clear
End If

Next

Set coll = Nothing

End Sub

2. Using a dictionary. set reference to ms.scripting runtime dll
Sub GetUniqueItems()
Dim cell As Range
Dim dic As Scripting.Dictionary

Set dic = New Scripting.Dictionary

For Each cell In Range("MyList").Cells

If Not dic.Exists(cell.Value) Then
dic.Add cell.Value, cell.Value
Sheet1.ComboBox1.AddItem cell.Value
End If

Next

Set dic = Nothing

End Sub


Patrick Molloy
Microsoft Excel MVP

"Ricky S" wrote:

How can I get enique records from a data list in a
worksheet to fill a listbox? I know how to add items to the
list box but isnt there a function to extract the records?

Thanks RS