  asked on

# Cumulative subtotals in Excel

I have the following code to run subtotals

``````    Range("A3").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(5, 6, 7, 8, 9, _
13, 14, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30), Replace:=True, _
PageBreaks:=False, SummaryBelowData:=True
Range("A1").Select
``````

I want to do the cumulative sum from Column 21 to 29

So the value in column 21 should be

(subtotal 20 + subtotal 21) = New value in column 21

The value in columns 22 should be

New value in column 21 + subtotal 22

And so on....
Microsoft Excel Last Comment
dlmille

8/22/2022 - Mon
dlmille

There is no cumulative function in the group subtotal command, however, what happens as you probably know is when that command is executed, Excel goes in and inserts rows/columns adding the subTotal() function.

So at this point, you merely need to go through that function row/column and update it.

Do you have a brief example spreadsheet that goes with this code?  I can better/more quickly help with this conversion if you'd post one.

Thanks,

Dave
fitaliano

There you go Dave, what I am trying to do is the progressive cumulative Cash Flow from 2006 to 2015.
Subtotal-Example.xls
dlmille

That's from U to AC?  I don't speak column numbers, so just confirming...

Dave
dlmille

I think that's right.

``````Option Explicit

Sub cumlSubtotals()
Dim wkb As Workbook
Dim wks As Worksheet
Dim lastRow As Long
Dim r As Range
Dim rRow As Range
Dim rng As Range

Set wkb = ThisWorkbook
Set wks = wkb.Sheets("Dashboard")

lastRow = wks.Range("A" & wks.Rows.Count).End(xlUp).Row

Set rng = wks.Range(wks.Range("U4:AC4"), wks.Range("U" & lastRow, "AC" & lastRow))

For Each rRow In rng.Rows
If Not r.Formula Like "=SUBTOTAL*" Then 'assume entire row to find has the subtotals, so save time skipping out if not
Exit For
Else 'found the subtotal row
r.Formula = "=" & r.Offset(, -1).Address & " + " & Right(r.Formula, Len(r.Formula) - 1)
End If
Next r
Next rRow
End Sub
``````

See attached demonstration workbook.

Dave
Subtotal-Example-r1.xls