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!

 

[SydneyGeek]

Here's a custom function that does the trick.
Place it in a new module (Alt+F11, Insert > Module, Paste, then Alt+q to return to Excel).
To count unique items in B2:B200 you would use this on a worksheet:

=CountUniqueVisible(B2:B200)
To use in VBA you could do something like
=CountUniqueVisible(Range("B2:B200"))

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)
    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

ExitHere:
    Set dic = Nothing
    Set Rng = Nothing
End Function

Denis

 

[jim may]

Denis, When I step thru your function, AND AFTER the below line -

Set Rng = Target.SpecialCells(xlCellTypeVisible)

in the immediate window when I do a ? Rng.address -- I get

$E$6:$E$16

versus what I would expect to see -- something like..

$E$6:$E$7,$E$9:$E$11,$E$14:$E$14,$E$16:$E$16

Am I thinking incorrectly?

Thanks,

Jim

 

[storm8]

Thank you very much!

I am assigning values to certain rows based on conditions. My spreadsheet has about 320.000 rows and 30 columns. I found out that it is faster to filter out the rows I want rather than getting the value by formulas.

so I filter criteria in each column until I get only the rows I want, then I fill the values for each visible row and clear filter. And loop for next set of criteria.

However when I get to column 10 it often happens that there are no more distinct values in col 10-30 and therefore further filtering is nor required. You seem to have quite an experience, do you think your srcipt will run faster than filtering nothing?

I have tested it, but could not really measure difference.

 

[storm8]

Denis, When I step thru your function, AND AFTER the below line -

Set Rng = Target.SpecialCells(xlCellTypeVisible)

in the immediate window when I do a ? Rng.address -- I get

$E$6:$E$16

versus what I would expect to see -- something like..

$E$6:$E$7,$E$9:$E$11,$E$14:$E$14,$E$16:$E$16

Am I thinking incorrectly?

Thanks,

Jim

you might have had incorrect filter, mine shows:
$D$3,$D$7,$D$10,$D$19:$D$21,$D$24,$D$26,$D$28,$D$42:$D$43,$D$45:$D$46,....

 

[SydneyGeek]

Jim, I ran a test. Without filtering I got a single range. With a filter applied I got the expected multi-range address.

storm8, I can't really comment on the speed of filtering vs not filtering while processing the records. However, setting Application.Calculation to Manual before running the filtering will make a difference. Excel runs a recalc every time you hide or unhide cells.

Denis

 

[jim may]

Not sure why, but here is my current results, using your UDF... E1 abd F1 are OK,
but C1 and D1 are not. hummmm
All results look more like function is returning the MAX number in filtered range.

Excel Workbook
	B	C	D	E	F
1	Current No of Unique Items in Filter ------->>	8	5	3	1
2
3	Fruits	Day1	Day2	Day3	Day4
5	Orange	8	2	2	1
21	Orange	1	3	3	1
23	Orange	4	5	2	1
25	Orange	1	2	1	1
Sheet1

Excel 2007

Cell Formulas
Range	Formula
C1	=CountUniqueVisible(C4:C25)
D1	=CountUniqueVisible(D4:D25)
E1	=CountUniqueVisible(E4:E25)
F1	=CountUniqueVisible(F4:F25)

 

[SydneyGeek]

How weird.

When you run it as a worksheet function it misbehaves. When you call it in VBA the result is the expected one. I'll take another look at it to see what's happening.

Denis

 

[Colin Legg]

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

 

[SydneyGeek]

That would explain it...

Thanks Colin

Denis