Create VBA macro to calculate unique value count with multitple criteria

Question

Below is an VBA function to calculate unique values count (Credit to SO: Count unique values in Excel) is it possible to add criteria paramaters? Like in the function "countifs"?

   Public Function CountUnique(rng As Range) As Integer
    Dim dict As Dictionary
    Dim cell As Range
    Set dict = New Dictionary
    For Each cell In rng.Cells
         If Not dict.Exists(cell.Value) Then
            dict.Add cell.Value, 0
        End If
    Next
    CountUnique = dict.Count
End Function

It depends on criteria. But probably you can play with the line that says If Not dict.Exists(cell.Value) Then adding your conditions there. Something like If Not dict.Exists(cell.Value) And cell.value > Parameter1 And cell.value < Parameter2 Then... Just an example — Foxfire And Burns And Burns

Vityata Vityata · Accepted Answer · 2018-07-25T11:04:35

This is what I have created:

The code looks like this:

Public Function CountUnique(inputRange As Range, paramRange As Range) As Long

    Dim dict As New Dictionary
    Dim cellInput As Range
    Dim cellParam As Range
    Dim keepLooking As Boolean

    For Each cellParam In paramRange
        keepLooking = True
        For Each cellInput In inputRange
            If cellParam.Value2 = cellInput.Value2 And keepLooking Then
                If Not dict.Exists(cellInput.Value2) Then
                    dict.Add cellInput, 0
                    keepLooking = False
                End If
            End If
        Next
    Next
    CountUnique = dict.Count

End Function

Create VBA macro to calculate unique value count with multitple criteria

1 Answers