 Working With Visible Cells  Visible Cells

Excel worksheet functions provide very limited support (via the SUBTOTAL function) for working with visible and hidden cells. However, it may be useful for your application to make calculations based on the values of only the visible cells, ignoring hidden rows and/or columns. This page describes a VBA function named VisibleCells that can be used in array formulas to allow calculations to be done only on visible (or only on hidden) cells. The code is shown below:

```Function VisibleCells(Rng As Range) As Variant
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' VisibleCells
' This function returns an array equal in dimension to the input
' parameter Rng containing 1's and 0's indicating whether a cells
' within Rng is visible. Note that we use 1 to indicate True rather
' than VBA's True value (which equals -1). If Rng has more than one
' area (discontiguous ranges), the function returns a #REF error.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim R As Range
Dim Arr() As Integer
Dim RNdx As Long
Dim CNdx As Long

''''''''''''''''''''''
' Ensure a valid range.
''''''''''''''''''''''
If Rng.Areas.Count > 1 Then
VisibleCells = CVErr(xlErrRef)
Exit Function
End If

'''''''''''''''''''''''''''''''''''''''''''''''''''
' Size the return array to equal the Rng parameter.
'''''''''''''''''''''''''''''''''''''''''''''''''''
ReDim Arr(1 To Rng.Rows.Count, 1 To Rng.Columns.Count)

For RNdx = 1 To Rng.Rows.Count
For CNdx = 1 To Rng.Columns.Count
Set R = Rng(RNdx, CNdx)
If (R.EntireRow.Hidden = True) Or _
(R.EntireColumn.Hidden = True) Then
Arr(RNdx, CNdx) = 0
Else
Arr(RNdx, CNdx) = 1 ' Use Excel's TRUE value
End If
Next CNdx
Next RNdx
''''''''''''''''''''''''''''''''''
' Return the result array
''''''''''''''''''''''''''''''''''
VisibleCells = Arr

End Function
```

In the function, the Rng parameter specifies the range of cells to test. The result is an array indicating whether each cell in Rng is visible (1) or hidden (0). This function can then be used in any formula or array formula. For example, you can average the visible values in a range with an array formula like the following:

=AVERAGE(IF(VisibleCells(A1:A5),A1:A5,FALSE)) For information about Array Formulas in general, see the array formuulas page. 