VBA Subtotal Median Function

[Dan1445]

Hello all,

I'm looking for a vba function that lets me have a subtotal that computes the median. I understand there is a method that uses "=MEDIAN(IF(SUBTOTAL(3,OFFSET(" but I am not looking for that. I want to be able to subtotal 10000+ lines of data with 700+ subtotals. When I record macro I get:

Columns("A:H").Select
Selection.Subtotal GroupBy:=2, Function:=xlCountNums, TotalList:=Array(7) _
, Replace:=True, PageBreaks:=False, SummaryBelowData:=True

But I'm looking for the function to be xlMedian. Thank you in advance for any help.

 

[Jerry Sullivan]

Hi Dan,

Sorry, but there isn't an xlMedian enumeration option- you'll need to find an alternative.

Perhaps you could use the SubTotal function with Count to create the Grouping Outline,
then use Replace to change the SubTotal Formulas to Median formulas.

Something like this....

Sub Median_SubTotals()
    With Sheets("Sheet1")
        With .Range("A1:H" & .Cells(.Rows.Count, "B").End(xlUp).Row)
            .Subtotal GroupBy:=2, Function:=xlCountNums, TotalList:=Array(7), _
                Replace:=True, PageBreaks:=False, SummaryBelowData:=True
        End With
        With .Range("G1:G" & .Cells(.Rows.Count, "G").End(xlUp).Row)
            .Replace What:="=SUBTOTAL(2,", Replacement:="=MEDIAN(", LookAt:=xlPart
            .Offset(0, -5).Replace What:="COUNT", Replacement:="MEDIAN"
            .Resize(1).Offset(.Rows.Count - 1).EntireRow.Delete
        End With
    End With
End Sub

This won't emulate all of SubTotal's behavior (like the option to exclude hidden cells or Grand Totals)
but hopefully it will be close enough for your purpose.

 

[Dan1445]

Jerry,

I apologize for the late reply, as I was on vacation. This worked great. It does exactly what I want it to do. Thank you.

 

[Jerry Sullivan]

You're welcome Dan. Thanks for following up when you returned.