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


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,

Nenhum comentário:

Postar um comentário

diHITT - Notícias