Count distinct values in an array

[Pats83]

Hello,
I am trying to get a count of the distinct values in an array.


myArray



653


123


653


255


123



I would like to get back a count of 3. I am not sure where to begin on this. I was thinking of looping through the array and having a variable then loop through the array again to compare the variable and have another variable hold the ubound count and -1 if it appears more than once.

I was hoping there would be a shorter way to accomplish this.
Thanks in advance

[Kenneth Hobs]

Be sure the add the reference as explained.


Sub Test()
Dim a() As Variant, b() As Variant
a() = Array(653, 123, 653, 255, 123)
MsgBox UBound(a)+1
b() = UniqueArrayByDict(a)
MsgBox UBound(b)+1
End Sub



' http://www.excelforum.com/excel-programming-vba-macros/819998-filter-and-sort-scripting-dictionary.html
'Early Binding method requires Reference: MicroSoft Scripting Runtime, scrrun.dll
'Tools > References > Microsoft Scripting Runtime
Function UniqueArrayByDict(Array1d() As Variant, Optional compareMethod As Integer = 0, _
Optional tfStripBlanks = False) As Variant
'Dim dic As Object 'Late Binding method - Requires no Reference
'Set dic = CreateObject("Scripting.Dictionary") 'Late or Early Binding method
Dim dic As Dictionary 'Early Binding method
Set dic = New Dictionary 'Early Binding Method
Dim e As Variant
dic.CompareMode = compareMethod
'BinaryCompare=0
'TextCompare=1
'DatabaseCompare=2
For Each e In Array1d
If Not dic.Exists(e) Then
If tfStripBlanks Or e <> "" Then dic.Add e, Nothing
End If
Next e
UniqueArrayByDict = dic.Keys
End Function

[Pats83]

Thanks Kenneth. I added in the code and it works perfectly.

[snb]

Sub M_snb()
sn = Array(653, 123, 653, 255, 123)
ReDim sp(UBound(sn))

For j = 0 To UBound(sn)
sp(j) = IIf(IsError(Application.Match(sn(j), sp, 0)), sn(j), "~")
Next

MsgBox Join(Filter(sp, "~", False), vbLf)
End Sub