SUBTOTAL() no longer needed!

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))