Count distinct values with Excel VBA

Question

I have this code to count distinct values, But I need it to only look at the visible range and filter range

Public Function CountDistinct(dataRange As Range) As Long
    'Populate range into array
    If dataRange.Rows.Count < 2 Then
        ReDim varTemp(1 To 1, 1 To 1)
        varTemp(1, 1) = dataRange
    Else
        varTemp = dataRange
    End If

    'Dictionaries can be used to store unique keys into memory
    Set dictTemp = New Dictionary

    'Add array items into dictionary if they do not exist
    For lngCounter = LBound(varTemp) To UBound(varTemp)
        If dictTemp.Exists(varTemp(lngCounter, 1)) = False Then
            dictTemp.Add Key:=varTemp(lngCounter, 1), Item:=1
        End If
    Next lngCounter

    'Count of unique items in dictionary
    CountDistinct = dictTemp.Count
End Function

Possible duplicate of Function to count distinct values in a column range — Tom
– Tom, Commented May 19, 2017 at 11:55

M-- · Accepted Answer · 2017-05-19 15:26:32Z

This is a function for this purpose from Mr. Excel:

Function CountUniqueVisible(Target As Range)
''==============================================
''Return the # of unique items in visible cells in a selected range
''Created 29 July 2011 by Denis Wright
''==============================================
Dim Rng As Range, _
    c As Range
Dim dic As Object
Dim y
Dim j As Long
Dim Sht As Worksheet
Dim strSheets As String

Set dic = CreateObject("Scripting.Dictionary")
Set Rng = Target.SpecialCells(xlCellTypeVisible)
j = 0
For Each c In Rng
    If Not dic.exists(c.Value) Then
        j = j + 1
        dic.Add c.Value, j
    End If
Next c
y = dic.keys
'Now we have a list of unique values. Next step is to return the count.
CountUniqueVisible = UBound(y) + 1

ExitHere:
    Set dic = Nothing
    Set Rng = Nothing
End Function

This works as a VBA function, not an UDF worksheet function.

Collectives™ on Stack Overflow

Count distinct values with Excel VBA

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related