## Intro

Four previous posts were a journey around Excel's SUBTOTAL() worksheet function
• It started with a need for someone to calculate averages from a row, ignoring only data in hidden columns
• A complement to SUBTOTAL() was provided.
• A replacement for SUBTOTAL() and it's complement
• A recommendation to overcome a shortcoming of the Excel object model

## Extravagant Boasts or Will Sonnett time?

This post has a User Defined Function that
• Makes SUBTOTALx() functions, both the Excel Worksheet function and the User Defined Functions provided in this series, obsolete.
• Can turn many worksheet functions that use arrays or ranges into one of those sub-functions of SUBTOTAL(), i.e. working while ignoring hidden data, without
• Extending the list of numbers to cover all applicable Worksheet functions.  It's tough enough to remember 1-11 and 101-111.  Those are in alphabetical order and any new worksheet Function from Microsoft would be given the next higher number...then there is overlap with the 101-111...Too much of a mess to try!
• Forcing users to type in the name of a Worksheet function as the first argument.
• Tested functions include: QUARTILE.EXC() & QUARTILE.INC(),  PERCENTILE.EXC() & PERCENTILE.INC(),
• It does not work
• When the Worksheet function needs a contiguous range.
• When using a LOOKUP() function or it's relatives, e.g. MATCH() and INDEX()
• Conditional functions such as : COUNTIF(), COUNTIFS(),  SUMIF() and SUMIF()

## The Function

As with the previous functions, this one walks through the ranges specified and includes them in the resulting range if the row and column are visible.  If the cell has already been included in the resulting range, the Union has no effect.

Function VisibleRange(ParamArray rngFull()) As Range
Dim rngVisible As Range
Dim rngCell As Range
Dim dblI As Double
For dblI = LBound(rngFull) To UBound(rngFull) ' Considers each range
For Each rngCell In rngFull(dblI).Cells ' Considers each cell in the current range
If Not (rngCell.Rows(1).Hidden Or rngCell.Columns(1).Hidden) Then
If rngVisible Is Nothing Then
Set rngVisible = rngCell ' Establishes the selected range
Else
Set rngVisible = Union(rngVisible, rngCell)
' Adds 2nd and subsequent cells to the output
End If
End If
Next rngCell
Next dblI
Set VisibleRange = rngVisible ' Assigns the collected range to the Function
End Function

## How to use it

The primary way to use VisibleRange() is to include it within other functions, e.g. =SUM(VisibleRange(B2:D24).  It is not limited to a single range.  Here are some variations that have been tested
• With disjoint ranges, e.g. =SUM(VisibleRange(B2:D24,X2:AA24, M2:M24))
• Without regard for the same number of rows or columns in the  disjoint ranges, e.g. =SUM(VisibleRange(B2:D24, T4:T5, E7:G7))
• With overlapping ranges, =SUM(VisibleRange(B2:D24, T4:T5, D7:G12)), the cells that are identified in more than one of the ranges (D7:D12 in this case) are used only once
• Without regard for the same number of rows or columns in the  disjoint ranges, e.g. =SUM(VisibleRange(B2:D24, T4:T5, E7:G7))
• With Named ranges, e.g. =SUM(VisibleRange(DataSet1, DataSet2))
• With Named ranges on multiple worksheet, e.g. =SUM(VisibleRange('Clyde Study'!DataPoints, 'Selma Research'!DataPoints))