How to count unique items in a column (VBA)

Send Us a Sign! (Contact Us!)

--> (Word)	--> (PDF)	--> (Text)

Show More Info

How many times this articles has been read? 57

This article has 103 total words. (Including header)

Function CountUnique(ByVal Rng As Range) As Long

Dim St As String

Set Rng = Intersect(Rng, Rng.Parent.UsedRange)

St = "'" & Rng.Parent.Name & "'!" & Rng.Address(False, False)

CountUnique = Evaluate("SUM(IF(LEN(" & St & "),1/COUNTIF(" & St & "," & St & ")))")

End Function

You could use it like:

MsgBox CountUnique(Sheets("Sheet1").Range("A:A"))

SOURCE	LINK	LANGUAGE	ENGLISH