Use any function considering visible cells only

E

Excelsior

Dear all,

I know the function SUBTOTAL and I often use it for summing the value
in visible cells of filtered lists.

My problem is that I want to use the function CORREL in the filtere
list.

I know the utility MOREFUNC, which adds a cool function to my Excel
something like "FILTERED_RANGE". Using this function, I can type th
following formula:

=CORREL(FILTERED_RANGE(A2:A50))

When I filter the list, only the visible cells (their values
respectively) are used for the calculation of CORREL.

I want to implement this additional function in my office, but I am no
allowed to install the mentioned utility. The only thing I am allowed t
do is to fill my PERSONAL.XLS with macros and user-defined functions.

Does anybody knows my problem and wrote such a macro (function) wit
the mentioned functionality ?

Thank you very much for any help.

Kind regards

Excelsio
 
B

Bob Phillips

roll your own

Function FilteredRange(ByVal rng As Range) As Variant
Dim ary
Dim cell As Range
Dim i As Long

ReDim ary(1 To rng.Rows.Count)

For Each cell In rng

If Not cell.EntireRow.Hidden Then

i = i + 1
ary(i) = cell.Value
End If
Next cell
ReDim Preserve ary(1 To i)
FilteredRange = ary
End Function
 
Top