vba: count of distinct values in filter range.

[storm8]

Hello,

I need a function to calculate number of distinct values in one column in a filtered range (including blanks)

So far I founf a formula:
=SUMPRODUCT((A1:A30000<>"")/COUNTIF(A1:A30000,A1:A30000&""))

- this works, but I need to tranfer it to application.worksheetfunction and apply it only for (xlCellTypeVisible)

so far I got to something like

d=application.WorksheetFunction.SumProduct(((range("t_rl[medium]").SpecialCells(xlCellTypeVisible)<>  & chr(34) &  chr(34))/application.WorksheetFunction.CountIf(range("t_rl[medium]").SpecialCells(xlCellTypeVisible),range("t_rl[medium]").SpecialCells(xlCellTypeVisible)  & chr(34) & chr34))))

but this will not work...

thanks alot!

 

[jim may]

What VBA code would be needed to call the UDF?

 

[jim may]

Never mind; I got it working... Tks, Jim

 

[postelrich]

Sydneygeek, I had been using your function for the past week or so, and first off thank you so much! It helps me out a lot.

I did notice one tiny problem while using this in a program. If a range inputed into it has only blank cells, it still returns 1. I added an if with a CountA to handle that. Wanted to share with you and see what you thought.

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)
    
    If Application.WorksheetFunction.CountA(Rng) > 0 Then
    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
    Else
    CountUniqueVisible = 0
    End If
ExitHere:
    Set dic = Nothing
    Set Rng = Nothing
End Function

 

[Weaver]

change

    For Each c In Rng
        If Not dic.exists(c.Value) Then
            j = j + 1
            dic.Add c.Value, j
        End If
    Next c

to

    For Each c In target
        If Not (dic.exists(c.Value) or c.entirerow.hidden) Then
            j = j + 1
            dic.Add c.Value, j
        End If
    Next c

 

[SydneyGeek]

Thanks postelrich, that makes sense -- I hadn't tested with a blank range.

Weaver, I'm unsure about the reason for the change you suggested. Doesn't only applying the range to visible cells make your modification redundant?

Denis

 

[Weaver]

Thanks postelrich, that makes sense -- I hadn't tested with a blank range.

Weaver, I'm unsure about the reason for the change you suggested. Doesn't only applying the range to visible cells make your modification redundant?

Denis

I thought someone said you couldn't use specialcells(xlcelltypevisible) in a UDF.

 

[SydneyGeek]

That's correct.

To call from a worksheet you'd need your option. I was thinking in terms of using from VBA, where the original is fine. Thanks for the contribution.

Denis

 

[jim may]

IIRC you can't use the Range.SpecialCells() method in a UDF.

Further comment needed to the above:

When the UDF is Inserted into a worksheet cell

The Range.SpecialCells() method CAN be used within VBA.

 

[SydneyGeek]

Taking postelrich and Weaver's suggestions on board, here's an update.
Works as a worksheet function, and with blank ranges.

Thanks guys

Function CountUniqueVisible(Target As Range)
''==============================================
''Return the # of unique items in visible cells in a selected range
''Created 29 July 2011 by Denis Wright
''Updated to work with blank ranges and
''as a worksheet function, 18 Aug 2011
''==============================================
    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)
 
    If Application.WorksheetFunction.CountA(Rng) > 0 Then
        j = 0
        For Each c In Rng
            If Not (dic.exists(c.Value) Or c.EntireRow.Hidden) 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
    Else
        CountUniqueVisible = 0
    End If
ExitHere:
    Set dic = Nothing
    Set Rng = Nothing
End Function

Denis

 

[remsr]

Hi,

how would you go about and modify this function to handle a range with several columns, and counting the uniqe values in only one column?

Say I have a range

ColA ColB ColC
A 4 Bill
A 5 Bill
A 3 Bill
A 4 Steve
A 4 Bill
B 4 steve
B 4 Bill
B 4 Bill

I would like to count uniqe values for ColB based on the values of Cola and ColC.
for example:
A and Bill would give count: 3 unique values of 4 values total
A and Steve would give: 1 unique values of 1 total
B and Steve would give: 1 unique values of 1 total
B and Bill would give: 1 unique values of 2 total

How would you go about to make a check for this?