Sub test()
MsgBox NBUNIQUE(Range("A1:A30"))
MsgBox NBUNIQUE2(Range("A1:A30"))
MsgBox NBUNIQUE3(Range("A1:A30"))
End Sub
Private Function NBUNIQUE(R As Range)
NBUNIQUE = Evaluate("=SUMPRODUCT(1/COUNTIF(" & R.Address & "," & R.Address & "))")
End Function
Private Function NBUNIQUE2(R As Range)
NBUNIQUE2 = Evaluate("Sum(N(countif(offset(" & R.Cells(1).Address & ",,,row(" & R.Address & "))," & R.Address & ")=1))")
End Function
Private Function NBUNIQUE3(R As Range)
Dim c As Range
With CreateObject("Scripting.Dictionary")
For Each c In R
If Not .Exists(c.Value) Then .Add c.Value, Nothing
Next
NBUNIQUE3 = .Count
End With
End Function