Recalculation using filter

J

Jeff

I'm caculating the number of unique values in a column,
but when the user uses a filter, the number does not
recalculate.

How do I make it recalculate the number of unique values?

Thanks!
 
G

Guest

Thanks for the suggestion, but I need to caluculate unique
values, and I don't know how to do that with Subtotal.
Here's my current caculation:

Range("J" & NumRows + 2).Formula =
"=SUM(IF(FREQUENCY(A7:A" & NumRows & ",A7:A" & NumRows & ")
0,1))"
Click to expand...


Any other thoughts?

Thanks
 
K

keepITcool

hmm..

I see you problem :(
subtotal wont work in combination with frequency..

afaik you'd need an UDF (vba function) to determine
row visibility as I dont think it can be done with a normal
worksheetfunction or even an old xl5 macro command



Function RowVisible(ref As Range)
Dim r As Long
With ref.EntireRow.Rows
ReDim v(1 To .Count) As Boolean
For r = 1 To .Count
v(r) = Not .Item(r).Hidden
Next
End With
RowVisible = v
End Function

Then

=Sum(n(frequency(a7:a12*n(rowvisible(a7:a12)),a7:a12)>0))


function should be available to the sheet
(which may be a problem...)

N() function converts a boolean to a value

it might work... it's just too complex.... sorry :(


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >
 
Top