Here is one way

Sub Macro1()
Dim iLastrow As Long
Dim ary

iLastrow = Cells(Rows.Count, "A").End(xlUp).Row
Columns("B:B").Insert
Range("B1").FormulaR1C1 = "=RC[-1]"
Range("B2").FormulaArray = _
"=IF(ISERROR(MATCH(0,COUNTIF(R1C:R[-1]C,R1C1:R" & iLastrow & _
"C1&""""),0)),""""," & Chr(10) & _
"INDEX(IF(ISBLANK(R1C1:R2000C1),"""",R1C1:R" & iLastrow & "C1)," & _
"MATCH(0,COUNTIF(R1C:R[-1]C,R1C1:R" & iLastrow & "C1&""""),0)))"
Range("B2").AutoFill Destination:=Range("B2:B" & iLastrow),
Type:=xlFillDefault
iLastrow = Evaluate("=SUMPRODUCT((A1:A" & iLastrow & "<"""")/" & _
"COUNTIF(A1:A" & iLastrow & "," & _
"A1:A" & iLastrow & "&""""))")
ary = Range("B1:B" & iLastrow)
Columns("B:B").Delete
End Sub


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"GreyPilgrim"
wrote in message
...

Does anyone know of an efficient way to:

Look at all of the cells in a selected column range,
identify all of the unique values in that column,
return each unique value into an array.

I think the nearest analogy I can think of is the GroupBy clause in
SQL, in Access I have a really really clunky vba routine at the moment
that basically says:

While not at the end of the list
If the current value is already in the array, don't do anything
If the current value is not in the array, add it in there
move to the next value in the list
Wend

Works fine for very small sets of data but obviously slows down for
very big data sets

Any help would be greatly appreciated!

Lee


--
GreyPilgrim
------------------------------------------------------------------------
GreyPilgrim's Profile:
http://www.excelforum.com/member.php...o&userid=31056
View this thread: http://www.excelforum.com/showthread...hreadid=507308