Here is a function that will take a range of cells and return the unique values in that sorted (this takes the place of the loop you showed part of above). So, if you wanted to assign the output to a variable named unique_string for the range of cells, say, C1:C10000, you would do this...
unique_string = Sorted(Range("C1:C10000"))
which, if the range C1:C10000 will never change, can be short-cutted to this...
unique_string = Sorted([C1:C10000])
Note: The Sorted function has an optional second argument allowing you to specify the delimiter to use which defaults to a comma if omitted.
Here is the function (put it in a General Module)...
Function Sorted(Rng As Range, Optional Delim As String = ",") As String
Dim X As Long, Arr As Variant, Uniques As Variant
Arr = Rng.Value
With CreateObject("Scripting.Dictionary")
For X = 1 To UBound(Arr)
.Item(Arr(X, 1)) = 1
Next
Uniques = .Keys
End With
With CreateObject("System.Collections.ArrayList")
For X = LBound(Uniques) To UBound(Uniques)
.Add Uniques(X)
Next
.Sort
Sorted = Join(.ToArray, Delim)
If Left(Sorted, 1) = Delim Then Sorted = Mid(Sorted, 2)
End With
End Function
Bookmarks