You can use a dictionary object to populate a unique array (it has an Exists method which can check for duplicates):
Dim cell As Range
Dim dic As Object
Dim myUniqueArray As Variant
Set dic = CreateObject("Scripting.Dictionary")
'use it to load the dictionary with all the unique values in A1:A1000:
For Each cell In Range("A1:A1000")
If Not dic.Exists(cell.Value) Then _
dic.Add cell.Value, cell.Value
'output to another range:
myUniqueArray = dic.Keys
Range("B1").Resize(UBound(myUniqueArray) - LBound(myUniqueArray) + 1) = Application.Transpose(myUniqueArray)
Thanks a lot!
This works great!
Could you please also tell me how can I see what value an array has at some time in the code like we do for variables either by placing the cursor or in the immediate window?