ThreeWave Working With Visible And Hidden Cells

This page describes how to work with visible and hidden cells.
ShortFadeBar

SectionBreak

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()
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' IsVisible
' This function returns an array of Boolean values indicating whether the
' corresponding cell in InRange is visible.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    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.

SectionBreak

Using IsVisible In Other Functions

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

-->