Working With Visible Cells

This page describes VBA code for 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.

This page last updated: 24-Oct-2008