You can do a similar thing with a Collection and then you don't need the
Scripting reference.
Or sort your array first, then step through the elements copy distinct
elements to a new array.

NickHK

"Lazzaroni" wrote in message
...
I was looking for a simple way to remove duplicates from an array;
something
that was equivalent to running a SELECT DISTINCT. I didn't find exactly
what
I was looking for but I found something close and modified it. Hopefully
someone else will find this useful, or perhaps even improve upon it.

Function DistinctArray(oInputArray, _
Optional MatchCase As Boolean = True, _
Optional OmitBlanks As Boolean = True)

'declare the variables
Dim oOutputArray As Variant
Dim oElement As Variant
Dim oDictionary As Dictionary

'create new dictionary object
'requires Microsoft Scripting Runtime reference
Set oDictionary = New Dictionary

'set case sensitivity
oDictionary.CompareMode = Abs(Not MatchCase)

'load elements from array into dictionary replacing duplicates
For Each oElement In oInputArray
oDictionary.Item(CStr(oElement)) = oElement
Next

'delete any blanks
If OmitBlanks Then
If oDictionary.Exists("") Then oDictionary.Remove ("")
End If

'load dictionary items into new array
oOutputArray = oDictionary.Items

'return the new array with distinct values only
DistinctArray = oOutputArray

End Function

The original function can be found he
http://home.pacbell.net/beban/