-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
CountDiff VBA
- Thread starter TeeMar
- Start date
I think I found a VBA that works similar to COUNTDIFF:
The custom function below counts the unique values only from a given range.
This function uses the fact that a collection can only hold items with unique identifications keys.
If you try to add an item with a key that already exists in the collection, an error will occur.
Function CountUniqueValues(InputRange As Range) As Long
Dim cl As Range, UniqueValues As New Collection
Application.Volatile
On Error Resume Next ' ignore any errors
For Each cl In InputRange
UniqueValues.Add cl.Value, CStr(cl.Value) ' add the unique item
Next cl
On Error GoTo 0
CountUniqueValues = UniqueValues.Count
End Function
Example:
=CountUniqueValues(A1:A100) will count the unique values in the range A1:A100
The custom function below counts the unique values only from a given range.
This function uses the fact that a collection can only hold items with unique identifications keys.
If you try to add an item with a key that already exists in the collection, an error will occur.
Function CountUniqueValues(InputRange As Range) As Long
Dim cl As Range, UniqueValues As New Collection
Application.Volatile
On Error Resume Next ' ignore any errors
For Each cl In InputRange
UniqueValues.Add cl.Value, CStr(cl.Value) ' add the unique item
Next cl
On Error GoTo 0
CountUniqueValues = UniqueValues.Count
End Function
Example:
=CountUniqueValues(A1:A100) will count the unique values in the range A1:A100
Upvote
0
Similar threads
- Solved
- Question
