Is a Collection the best option?

[Patrick Molloy]

You've had great responses so far. I use the Scripting
Dictionary most of the time. A dictionary is basically a
collection and works similarly to a collection. However,
there are some very good methods available - for example
the Rxists method allows you to test if a key exists...
If NOT MyDic.Exists(ThisKey) then
MyDic.Add Item, ThisKey
End If

One can loop through both the items AND the Keys which is
really useful.

As an alternative, I also use disconnected recordsets.
These are powerful tools to use in code, as they allow
sorting, filetering etc of data exactly as if one had
used a SQL statement.

In the following example, I have a column of data in a
sheet starting in the third row of column "D". I've
created a recordset to extract the unique items, and used
the recordset's Sort method to drop the list of uique
data into the sheet.

Sub GetValues()
Dim rw As Long ' loop index
Dim sText As String ' cell value
Dim rst As New ADODB.Recordset

' create & open the recordset
With rst
.Fields.Append "KeyName", adChar, 20
.Open
End With

'initialise the row index
rw = 3

'loop for each cell in the column
Do Until Cells(rw, "D") = ""
' get the cell's value
sText = Cells(rw, "D").Value
' check if we already have it in the recordset
If Not rst.BOF Then rst.MoveFirst
rst.Find "[KeyName]='" & sText & "'" ', _
, adSearchForward, True
If rst.EOF Then
' no, then add it
rst.AddNew 0, sText
End If

' increent index for then next cell
rw = rw + 1
Loop

' now sort the recordset
rst.Sort = "KeyName ASC"
'rst.MoveFirst
Range("A1").CopyFromRecordset rst

rst.Close

Set rst = Nothing

End Sub

The project requires a reference to the Active Data
Objects 2.7 Library

HTH
Patrick Molloy
Microsoft excel MVP