SUBTOTAL()-like UDF for cases when Rows and Columns may be hidden

Intro

• AVERAGE() UDF excluding hidden columns...plus SUM(), COUNT(), etc introduced the ability to get averages or sums or results of other functions for rows, using only the values in visible columns. One sub-function of  the SUBTOTAL() worksheet function, turned on its side
• Interpretation of SUBTOTAL() operations argument explains the difference between the 1-11 range of arguments and the 101-111 range.
• SUBTOTAL()-like UDF for rows with hidden columns expanded this into a full complement to the SUBTOTAL() worksheet function.

The Opportunity

Thinking about it further I realized there are times when SUBTOTALS might be needed for cases when some rows and  some columns are hidden.  Consider, for example,  tables of data where a reading is taken periodically each day (days as columns and times of day labeling each row, or vice versa):   meteorological readings, manufacturing outputs,  and 24x7 schedules.

A SUBTOTAL() for a range

The Subtotal() worksheet function can be used on multiple columns of data, ignoring what is hidden by row.  The user-defined function in y ignored what was hidden by column and can used on multiple rows. One small change to the code in y (highlighted below) results a SUBTOTAL function that ignores the cell if hidden by row or hidden by column.

Function SubTotalColumns(Operation As Integer, ParamArray rngSource())

    Application.Volatile

    Dim rngCell As Range

    Dim rngVisible As Range

    Dim dblCtr As Double

    For dblCtr = LBound(rngSource) To UBound(rngSource)

        For Each rngCell In rngSource(dblCtr).Cells

            If Not (rngCell.Columns.Hidden Or rngCell.Rows.Hidden) Then 

               ' Instead of  "Not rngCell.Columns(1).Hidden"

                If rngVisible Is Nothing Then

                    Set rngVisible = rngCell

                Else

                    Set rngVisible = Union(rngVisible, rngCell)

                End If

            End If

        Next rngCell

    Next dblCtr

    Select Case Operation

        Case 1

            SubTotalColumns = Application.WorksheetFunction.Average(rngVisible)

        Case 2

            SubTotalColumns = Application.WorksheetFunction.count(rngVisible)

        Case 3

            SubTotalColumns = Application.WorksheetFunction.CountA(rngVisible)

        Case 4

            SubTotalColumns = Application.WorksheetFunction.max(rngVisible)

        Case 5

            SubTotalColumns = Application.WorksheetFunction.min(rngVisible)

        Case 6

            SubTotalColumns = Application.WorksheetFunction.Product(rngVisible)

        Case 7

            SubTotalColumns = Application.WorksheetFunction.StDev(rngVisible)

        Case 8

            SubTotalColumns = Application.WorksheetFunction.StDevP(rngVisible)

        Case 9

            SubTotalColumns = Application.WorksheetFunction.Sum(rngVisible)

        Case 10

            SubTotalColumns = Application.WorksheetFunction.var(rngVisible)

        Case 11

            SubTotalColumns = Application.WorksheetFunction.varP(rngVisible)

        End Select

End Function