Operations on union of ranges

[Mifrey]

Hello all,

I use a function that returns an union of all visible cells:

Function Vis(Rin As Range) As Range
'Returns the subset of Rin that is visible
Dim cell As Range
Application.Volatile
Set Vis = Nothing
For Each cell In Rin
If Not (cell.EntireRow.Hidden Or cell.EntireColumn.Hidden) Then
If Vis Is Nothing Then
Set Vis = cell
Else
Set Vis = Union(Vis, cell)
End If
End If
Next cell
End Function

The function above works well and I can use it for example to
calculate the sum with '=SUM(Vis(A1:A9))'.
But some functions do not work, for example '=CORREL(Vis(A1:A9),Vis
(B1:B9))' give me a '#VALUE!' if the union is a union of more than one
range. How can I calculate the correlation?

 

[Bernie Deitrick]

Michael,

CORREL only works on two single area ranges - your VIS may return ranges of multiple areas.

If you use your code to return an array rather than a multi-area range (see code for function Vis2,
below), it will work, like so:

=CORREL(Vis2(A1:A9),Vis2(B1:B9))


HTH,
Bernie
MS Excel MVP


Function Vis2(Rin As Range) As Variant
'Returns the subset of Rin that is visible
Dim cell As Range
Dim myV() As Variant
Dim Cntr As Integer

Application.Volatile
ReDim myV(1 To 1)

Cntr = 1
For Each cell In Rin
If Not (cell.EntireRow.Hidden Or cell.EntireColumn.Hidden) Then
If Cntr = 1 Then
myV(1) = cell.Value
Cntr = Cntr + 1
Else
ReDim Preserve myV(1 To Cntr)
myV(Cntr) = cell.Value
End If
End If
Next cell

Vis2 = myV
End Function

 

[Mifrey]

Thanks it works !
Except 'Cntr = Cntr + 1' that was not well placed.

Function Vis2(Rin As Range) As Variant
'Returns the subset of Rin that is visible
Dim cell As Range
Dim myV() As Variant
Dim Cntr As Integer

Application.Volatile
ReDim myV(1 To 1)

Cntr = 1
For Each cell In Rin
If Not (cell.EntireRow.Hidden Or cell.EntireColumn.Hidden) Then
If Cntr = 1 Then
myV(1) = cell.Value
Else
ReDim Preserve myV(1 To Cntr)
myV(Cntr) = cell.Value
End If
Cntr = Cntr + 1
End If
Next cell

Vis2 = myV
End Function