Use any function considering visible cells only
 

Dear all,

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

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

I know the utility MOREFUNC, which adds a cool function to my Excel;
something like "FILTERED_RANGE". Using this function, I can type the
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 not
allowed to install the mentioned utility. The only thing I am allowed to
do is to fill my PERSONAL.XLS with macros and user-defined functions.

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

Thank you very much for any help.

Kind regards

Excelsior


--
Excelsior
------------------------------------------------------------------------
Excelsior's Profile: http://www.thecodecage.com/forumz/member.php?userid=100
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=52600

Use any function considering visible cells only
 

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

--
__________________________________
HTH

Bob

"Excelsior" wrote in message
...

Dear all,

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

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

I know the utility MOREFUNC, which adds a cool function to my Excel;
something like "FILTERED_RANGE". Using this function, I can type the
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 not
allowed to install the mentioned utility. The only thing I am allowed to
do is to fill my PERSONAL.XLS with macros and user-defined functions.

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

Thank you very much for any help.

Kind regards

Excelsior


--
Excelsior
------------------------------------------------------------------------
Excelsior's Profile:
http://www.thecodecage.com/forumz/member.php?userid=100
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=52600