Working With Visible And Hidden Cells
This page describes how to work with visible and hidden cells.
Excel's built in worksheet functions provide almost no support for working with hidden ranges. The
closest you can get is the SUBTOTAL function, but this recognizes only
cells that are hidden by Excel's Filter tools. It does not support rows or columns that are hidden
normally.
However, with a simple VBA function, you can work with hidden cells on a worksheet. The basic VBA
function is shown below:
Public Function IsVisible(InRange As Range) As Boolean()
Dim R As Range
Dim Arr() As Boolean
Dim RNdx As Integer
Dim CNdx As Integer
ReDim Arr(1 To InRange.Rows.Count, 1 To InRange.Columns.Count)
For RNdx = 1 To InRange.Rows.Count
For CNdx = 1 To InRange.Columns.Count
Set R = InRange(RNdx, CNdx)
If R.EntireRow.Hidden = True Or R.EntireColumn.Hidden = True Then
Arr(RNdx, CNdx) = False
Else
Arr(RNdx, CNdx) = True
End If
Next CNdx
Next RNdx
IsVisible = Arr
End Function
This function takes as an input parameter the range of cells to test. It returns as its result
an array of Boolean (True or False) values indicating whether the corresponding cell in the range
specified by InRange is visible (True) or hidden (False). In almost all
circumstances, you will use the IsVisible function in an
array formula. Because IsVisible returns
an array, you can aggregate the result of the function in using other functions such as
SUM, or AVERAGE.
You can use the result of IsVisible in other formulas, typically in array formulas
(ArrayFormulas.aspx). The following are examples of using the
IsVisible function to peform various operations. All of the formulas
below are array formulas so you must press
CTRL SHIFT ENTER rather than just ENTER when you first enter the formula and
whenever you edit it later.
Count Of Visible Cells
=SUM(--IsVisible(D11:D20))
This returns the number of visible cells in the range D11:D20.
The double negation (--) converts the Boolean
values returned by
IsVisible to their numeric equivalents (True = 1, False
= 0).
Sum Of Visible Cells
=SUM(IF(IsVisible(D11:D20),D11:D20,FALSE))
This returns the sum of the values in the visible cells within the range
D11:D20. The same result can also be found with the shorter function
=SUM(IsVisible(D11:D20)*D11:D20)
Average Of Visible Cells
=AVERAGE(IF(IsVisible(D11:D20),D11:D20,FALSE))
This returns the average of visible cells in the range D11:D20.
This page last updated: 4-September-2008