I am trying to add values to a collection to display unique values. I have a
worksheet with comma separated values in a of column of cells. I am using the
Split function to loop through the values and add them to the Collection.
There are duplicate values in the string, I do not understand why they are
still added to the Collection.

Public Function RemoveDupes(c As Range) As String
Dim i As Integer
Dim itm
Dim strItems As String
Dim nStr() As String
Dim nColl As New Collection

nStr = Split(c, ",")
For i = 0 To UBound(nStr)
nColl.Add Item:=nStr(i)
Next

For Each itm In nColl
Debug.Print itm
strItems = strItems & itm & ","
Next

If Not Len(strItems) = 0 Then
RemoveDupes = Left(strItems, Len(strItems) - 1)
Else
RemoveDupes = ""
End If
Set nColl = Nothing
End Function

A Collection may contain duplicate values but not duplicate keys. Try
something like this

Sub test()
Dim i As Long
Dim s As String
Dim sa() As String
Dim col As Collection

s = "a,b,a,c,a,d"
sa = Split(s, ",")

Set col = New Collection
On Error Resume Next
For i = 0 To UBound(sa)
col.Add sa(i), sa(i)
Next

For i = 1 To col.Count
Debug.Print col(i)
Next

End Sub

Regards,
Peter T


"Ralph" wrote in message
...
I am trying to add values to a collection to display unique values. I have
a
worksheet with comma separated values in a of column of cells. I am using
the
Split function to loop through the values and add them to the Collection.
There are duplicate values in the string, I do not understand why they are
still added to the Collection.

Public Function RemoveDupes(c As Range) As String
Dim i As Integer
Dim itm
Dim strItems As String
Dim nStr() As String
Dim nColl As New Collection

nStr = Split(c, ",")
For i = 0 To UBound(nStr)
nColl.Add Item:=nStr(i)
Next

For Each itm In nColl
Debug.Print itm
strItems = strItems & itm & ","
Next

If Not Len(strItems) = 0 Then
RemoveDupes = Left(strItems, Len(strItems) - 1)
Else
RemoveDupes = ""
End If
Set nColl = Nothing
End Function

Thank you! I had been fooling with that for hours. Made the change, works
like a charm now.

"Peter T" wrote:

A Collection may contain duplicate values but not duplicate keys. Try
something like this

Sub test()
Dim i As Long
Dim s As String
Dim sa() As String
Dim col As Collection

s = "a,b,a,c,a,d"
sa = Split(s, ",")

Set col = New Collection
On Error Resume Next
For i = 0 To UBound(sa)
col.Add sa(i), sa(i)
Next

For i = 1 To col.Count
Debug.Print col(i)
Next

End Sub

Regards,
Peter T


"Ralph" wrote in message
...
I am trying to add values to a collection to display unique values. I have
a
worksheet with comma separated values in a of column of cells. I am using
the
Split function to loop through the values and add them to the Collection.
There are duplicate values in the string, I do not understand why they are
still added to the Collection.

Public Function RemoveDupes(c As Range) As String
Dim i As Integer
Dim itm
Dim strItems As String
Dim nStr() As String
Dim nColl As New Collection

nStr = Split(c, ",")
For i = 0 To UBound(nStr)
nColl.Add Item:=nStr(i)
Next

For Each itm In nColl
Debug.Print itm
strItems = strItems & itm & ","
Next

If Not Len(strItems) = 0 Then
RemoveDupes = Left(strItems, Len(strItems) - 1)
Else
RemoveDupes = ""
End If
Set nColl = Nothing
End Function

Hello,

You might want to get insured against empty cells/values:
coll.Add lngIndex, "X" & sPattern

See
http://sulprobil.com/html/lfreq.html
UDF List_Freq...

Regards,
Bernd

Well Done Peter. This is brilliant! [And QUICK!]