Function to count unique characters within a range

[Leandroarb]

Hello everyone!


I needed to count the unique characters within a range and set up this simple function.

I share here for two reasons:


1 - Can be used for someone else
2 - Surely, the masters in excel of this forum will improve it infinitely.


Thank you in advance.

Public Function AccountSingles(Rng As Range) 'Counts unique characters within a sequence
Dim strRegs As String 'For a text string of characters
Dim cont As Integer 'Counter
Dim c As Range


For Each c In Rng
    If Application.WorksheetFunction.CountIf(Rng, c) > 0 And InStr(strRegs, c) = 0 Then 'If the character is counted inside the range, but if it is not in the sequence where the loop has                                                                                                                                          'passed ...
        cont = cont + 1 'Add one more to the counter
        Else 'Otherwise...
        cont = cont 'Keeps the counter with the same value
    End If
    strRegs = strRegs & c & ", " 'Adds the last character to the string
Next
AccountSingles = cont 'At the end, it assigns the total value to the function
End Function

 

[DanteAmor]

How about

Function cSingles(rng As Range)
Dim c As Range, n As Long
For Each c In rng
    If WorksheetFunction.CountIf(Range(c, Cells(rng.Cells(1).Row + rng.Count - 1, 1)), c) = 1 Then n = n + 1
Next
cSingles = n
End Function

 

[Leandroarb]

Tested?

Explain for all please!

Thanks for your contribuition!

How about

Function cSingles(rng As Range)
Dim c As Range, n As Long
For Each c In rng
    If WorksheetFunction.CountIf(Range(c, Cells(rng.Cells(1).Row + rng.Count - 1, 1)), c) = 1 Then n = n + 1
Next
cSingles = n
End Function

 

[DanteAmor]

Tested?

Explain for all please!

Thanks for your contribuition!

Of course.
I invite you to take a test and tell me.

Basically it does the same thing, a cycle for each data of the range.

[COLOR=#333333]For Each c In rng[/COLOR]

Counts the number of values ​​from the cell to the end of the range. If the account is 1, then it is unique and adds 1 to the counter.

[COLOR=#333333][I]Range(  from c, to end of range )[/I][/COLOR]