mg not sure why U want to use collections and dictionary??? I haven't looked at your file but I assume that it has some dictionary code that also doesn't work? Your code might even work with the correct syntax? There are a number of ways to get and transfer unique data. Here's a simple array method to load an array with unique values which then can be unloaded wherever. HTH. Dave
Function UniqueArr(InArr As Variant) As Variant
'returns array of unique values from inputted array
Dim Cnt As Integer, Cnt2 As Integer, Cnt3 As Integer, TempArr() As Variant
For Cnt = UBound(InArr) - 1 To LBound(InArr) Step -1
For Cnt2 = Cnt - 1 To 0 Step -1
If InArr(Cnt) = InArr(Cnt2) Then
GoTo below
End If
Next Cnt2
ReDim Preserve TempArr(Cnt3)
TempArr(Cnt3) = InArr(Cnt)
Cnt3 = Cnt3 + 1
below:
Next Cnt
UniqueArr = TempArr
End Function
To operate would be something like...
Dim ArrTemp() as Variant, Arr() as Variant, Lr s double
lr = Sheets("Sheet1"). Range("a65000").End(xlUp).Row
Set ArrTemp = Sheets("Sheet1").Range("a1:a" & lr)
Set Arr = UniqueArr(ArrTemp)
The array Arr should then contain an array of unique values