Friday, September 26, 2014

SUBTOTAL()-like UDF for rows with hidden columns


The code AVERAGE for unhidden columns...and SUM and COUNT provides a focused solution for someone who wants to be able to find the average of cells in a row, excluding those in hidden columns.  In a sense, all she wanted was the Worksheet function to work horizontally as well as vertically.  That's not unreasonable.  

Building on success

The one-function-at-a-time focused solution is the basis for a function that can be used for rows instead of columns.  All that was needed was a SELECT CASE ... END CASE.
  • To be consistent, to uses the same inputs as the SUBTOTAL function, i.e. the Operation and the target ranges.
  • The 1-11 operations values (1 for Average, etc) are omitted because I did not want to continue the confusion of the poorly described difference between the 1-11 range and the 1-111 range.  Hidden is Hidden, and my understanding of the 101-111 range is more in line with the behavior I think I see.  Either way, it would be simple to change back to the 1-11 range.

Function SubTotalColumns(Operation As Integer, ParamArray rngSource())
    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
                    Set rngVisible = Union(rngVisible, rngCell)
                End If
            End If
        Next rngCell
    Next dblCtr
    Select Case Operation
        Case 101
            SubTotalColumns = Application.WorksheetFunction.Average(rngVisible)
        Case 102
            SubTotalColumns = Application.WorksheetFunction.count(rngVisible)
        Case 103
            SubTotalColumns = Application.WorksheetFunction.CountA(rngVisible)
        Case 104
            SubTotalColumns = Application.WorksheetFunction.max(rngVisible)
        Case 105
            SubTotalColumns = Application.WorksheetFunction.min(rngVisible)
        Case 106
            SubTotalColumns = Application.WorksheetFunction.Product(rngVisible)
        Case 107
            SubTotalColumns = Application.WorksheetFunction.StDev(rngVisible)
        Case 108
            SubTotalColumns = Application.WorksheetFunction.StDevP(rngVisible)
        Case 109
            SubTotalColumns = Application.WorksheetFunction.Sum(rngVisible)
        Case 110
            SubTotalColumns = Application.WorksheetFunction.var(rngVisible)
        Case 111
            SubTotalColumns = Application.WorksheetFunction.varP(rngVisible)
        End Select
End Function