VBA to add subtotal with variable range

Question

I'm trying to figure out excel VBA code to add subtotal (=SUBTOTAL(9,__:_) formulas to multiple Total Rows within a worksheet. The issue is that the range for the subtotals can be any height. Per the image below, the hope is that the VBA code would be able to identify when "Total" is present in column B and then proceed to add a subtotal formula in that row for columns E to J. The subtotal formula would need to pull the above balances up until there is a blank cell in order to calculate the amount for the particular client (the length of the range is signified by the arrows in the image below). The range can litterally be any number of rows so has to be fully variable. Any help in this would be greatly appreciated!!

Updated Sample

Why subtotal and not sum? Are you planning to hide rows you don't want in the total? — user4039065
I'd like to use subtotal bc I'd also like to add a line at the very bottom of the list that calculates the total for all of the clients. Subtotal would allow to just take the entire range up and calculate the entire current balance. Does that makes sense? — User22
I guess I should have included that in my question :) Thank you so much for the help on this!! — User22

Unknown Unknown · Accepted Answer · 2018-07-14T15:45:40

This can be accomplished with a simple SUBTOTAL across a dynamic range. Locating Total in column B can be looped.

Option Explicit

Sub totalAllClients()
    Dim rng As Range, rngsb As Range, addr As String

    With Worksheets("sheet11")
        With Intersect(.Columns(2), .UsedRange)
            Set rng = .Find(What:="total", After:=.Cells(1), MatchCase:=False, _
                            LookAt:=xlWhole, SearchDirection:=xlPrevious)
            If Not rng Is Nothing Then
                addr = rng.Address(0, 0)
                Do
                    rng.Offset(0, 1).FormulaR1C1 = _
                        "=index(c2, match(""zzz"", r1c2:r[-1]c2))"
                    rng.Offset(0, 3).Resize(, 6).FormulaR1C1 = _
                        "=subtotal(109, r[-1]c:index(c, match(""zzz"", r1c2:r[-1]c2)))"
                    Set rng = .FindNext(After:=rng)
                Loop Until rng.Address(0, 0) = addr

                rng.Offset(2, 3).Resize(1, 6).FormulaR1C1 = _
                    "=aggregate(9, 3, r2c:r" & rng.Row & "c)"
            End If
        End With
    End With

End Sub

VBA to add subtotal with variable range

1 Answers