Collection of case sensitive unique items

[Alan Beban]

Given red, Red, Blue, blue, blue in A1:A5,

Dim x as New Collection
dim rng as Range
Set rng = Range("A1:A5")
On Error Resume Next
For each iVal in rng
x.Add Item: = iVal, key:= CStr(iVal)
Next

will produce a two-element collection of red and Blue. How can I make
the collection of unique items case sensitive; i.e., a 4-element
collection of red, Red, Blue, blue?

Thanks in advance.

Alan Beban

 

[Jim Cone]

Hello Alan,

You can do it with a Dictionary object...
'--------------------------------
'Requires project reference to the "Microsoft Scripting Runtime"
Sub Test()
Dim x As Scripting.Dictionary
Dim Rng As Range
Dim iVal As Range

Set x = New Scripting.Dictionary
Set Rng = Range("A1:A5")

On Error Resume Next
For Each iVal In Rng
x.Add key:=iVal.Text, Item:=iVal
Next
On Error GoTo 0
Range(Cells(1, 2), Cells(1, x.Count + 1)).Value = x.Keys

Set Rng = Nothing
Set iVal = Nothing
Set x = Nothing
End Sub
'--------------------------------

Regards,
Jim Cone
San Francisco, CA

 

[Alan Beban]

Hi Jim,

Thanks. I wouldn't have worked it out on my own.

Alan Beban

 

[Jim Cone]

Alan,

You probably would find the Windows Script documentation file of interest - available from...

http://msdn.microsoft.com/library/default.asp?url=/downloads/list/webdev.asp
Microsoft Windows Script 5.6 (Windows 2000, XP)

Regards,
Jim Cone
San Francisco, CA

 

[Alan Beban]

Thanks.

Alan,

You probably would find the Windows Script documentation file of interest - available from...

http://msdn.microsoft.com/library/default.asp?url=/downloads/list/webdev.asp
Microsoft Windows Script 5.6 (Windows 2000, XP)

Regards,
Jim Cone
San Francisco, CA