WARNING: HIGH LEVEL OF OPINION about a certain software company
The Direct Answer
- 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.VolatileDim rngCell As RangeDim rngVisible As RangeDim dblCtr As Double
For dblCtr = LBound(rngSource) To UBound(rngSource)For Each rngCell In rngSource(dblCtr).Cells
If Not rngCell.Columns(1).Hidden ThenIf rngVisible Is Nothing ThenSet rngVisible = rngCellElseSet rngVisible = Union(rngVisible, rngCell)End IfEnd IfNext rngCellNext dblCtrNext dblCtrAveUnhiddenColumns = WorksheetFunction.Average(rngVisible)End Function
- 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.