AVERAGE() UDF excluding hidden columns...plus SUM(), COUNT(), etc.

WARNING:  HIGH LEVEL OF OPINION about a certain software company

The Problem

This week's question from one of Alan's subscribers was how to get subtotaling working when hiding columns instead of rows. She wanted to use the Average sub-function.  Following is an edited version of my response to Alan, plus a lot more.

First Discovery

I hadn't used the SUBTOTAL() function in years, which made me think the answer was simply to use it. 

Then I re-learned that SUBTOTAL() only works on hidden rows, primarily used with filters.  This seems very short-sighted of Microsoft as I can think of reasons when columns might be hidden as people consider budgeting options, excluding certain days of the week for operations reporting, etc. This makes me wonder how many times we've structured our worksheets to fit the tools provided.

IOW, the subscriber doesn't have an issue, Microsoft does.

The Direct Answer

Here is an approach that provides an answer to the subscribers immediate question, i.e. how to do an average of only the visible columns.  

• The function walks through the ranges in rngSource(), inspecting each cell to see if its column is Hidden.
• UNION combines the cells that are not Hidden into rngVisible.
• The UNION approach eliminates double counting as would happen if rngSource() included overlapping ranges
• rngVisible is then used as input to WorksheetFunction.Average.
• Using a Worksheet function instead of writing code for the  calculations takes advantage of the error checking and reporting.

Function AveUnhiddenColumns(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(1).Hidden Then

                If rngVisible Is Nothing Then

                    Set rngVisible = rngCell

                Else

                    Set rngVisible = Union(rngVisible, rngCell)

                End If

            End If

        Next rngCell

    Next dblCtr

    Next dblCtr

    AveUnhiddenColumns = WorksheetFunction.Average(rngVisible)

End Function

What's next?

This has run long so I've decided to break the rest into other postings.  Here they are in the intended order of reading.

• SUBTOTAL() function for rows with hidden columns
• Interpretation of SUBTOTAL() operations argument
• SUBTOTAL() function useful when Rows and/or Columns may be hidden
• Shortcoming of Object Model Affects re-written SUBTOTAL() functions
• SUBTOTAL() no longer needed.