Working With Visible Cells
This page describes VBA code for working with 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
Dim R As Range
Dim Arr() As Integer
Dim RNdx As Long
Dim CNdx As Long
If Rng.Areas.Count > 1 Then
VisibleCells = CVErr(xlErrRef)
Exit Function
End If
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
End If
Next CNdx
Next RNdx
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))
This page last updated: 24-Oct-2008