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,