VBA Excel - Conte Ocorrências Distintas num Range - Count Distinct Or Unique Values - VBA UDF

Consultancy in Excel, Access, VBA, Dashboards, Scorecards & PowerPoint Projects

Perguntas, Questões e Esclarecimentos

Talvez precise contar especificamente quantas ocorrências distintas existem num Range de dados. 

Por exemplo: a, a, b, b, c, d, e, e, f = 5

Aqui está a solução fácil e rápida:

Public Function COUNTDISTINCTcol (ByRef rngToCheck As Range) As Variant

    Dim colDistinct As Collection

    Dim varValues As Variant, varValue As Variant

    Dim lngCount As Long, lngRow As Long, lngCol As Long

    On Error GoTo ErrorHandler

    varValues = rngToCheck.Value

    'if rngToCheck is more than 1 cell then

    'varValues will be a 2 dimensional array

    If IsArray(varValues) Then

        Set colDistinct = New Collection

        For lngRow = LBound(varValues, 1) To UBound(varValues, 1)

            For lngCol = LBound(varValues, 2) To UBound(varValues, 2)

                varValue = varValues(lngRow, lngCol)

                'ignore blank cells and throw error

                'if cell contains an error value

                If LenB(varValue) > 0 Then

                    'if the item already exists then an error will

                    'be thrown which we want to ignore

                    On Error Resume Next

                    colDistinct.Add vbNullString, CStr(varValue)

                    On Error GoTo ErrorHandler

                End If

            Next lngCol

        Next lngRow

        lngCount = colDistinct.Count

    Else

        If LenB(varValues) > 0 Then

            lngCount = 1

        End If

    End If

    COUNTDISTINCTcol = lngCount

    Exit Function

ErrorHandler:

    COUNTDISTINCTcol = CVErr(xlErrValue)

End Function

Tags: Excel, distinct, distinto, occurs, ocorrências,