Count items via VBA that occur only once in a range

[NewOrderFac33]

Good afternoon,

Assuming I have the following items in a selected range: 1,2,2,2,3,4,4,5,6,6,7,7,8,8,9
can anyone suggest a VBA solution to display the number of items that occur in the list only ONCE?

In the above example, the answer would be 4 as only four numbers occur uniquely in the list (1, 3, 5 and 9).

If you can make it display those values too (e.g. "There are 4 items that occur only once: 1, 3, 5 and 9", that would be a bonus!)

As always, thanks in advance

Pete

 

[Fluff]

How about

Sub NewOrderFac33()
   Dim Cl As Range
   Dim Ky As Variant
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Selection
         If Not .Exists(Cl.Value) Then
            .Add Cl.Value, True
         Else
            .Item(Cl.Value) = False
         End If
      Next Cl
      For Each Ky In .Keys
         If Not .Item(Ky) Then .Remove Ky
      Next Ky
      Debug.Print Join(.Keys, ", "); " Are unique"
   End With
End Sub

 

[NewOrderFac33]

Absolutely perfect - thank you VERY much!

Pete

 

[Fluff]

You're welcome & thanks for the feedback