Adding a row to a subtotalled range

[Mickey Mouse]

I have a spreadsheet that contains sales values for each month. As there can be more than one value for a month I am using the subtotal function to aggregate the sales to give me twelve values (one for each month). The spreadsheet is maintained on an ongoing basis, so new entries are added from time to time. The data range also has an auto filter so that I can select the records for any particular month if needed.

Currently, February only has one record. After applying the subtotal function to the range, I have added a second record for February, but the subtotal does not update. I am adding the second record by inserting a row and entering the values. However, the subtotal does not update itself irrespective of whether I add the row before or after the existing record.

If a month contains more than one record to begin with, I can insert a third row in between the two existing records and the subtotal will update automatically. The problem is specific to only having one record when the subtotal is set up in the first instance.

Example:

Month Sales($)
Jan 100
Jan 150
Jan 100
Jan Total 350
Feb 250
Feb Total 250
Mar 120
Mar 140
Mar Total 260

In the above example, The Feb Subtotal will not automatically update no matter where I insert a second record for Feb.

Help!

 

[Trevor Shuttleworth]

Mickey

when you add subtotals it generates a formula, something like: =SUBTOTAL(9,B6:B6)

Note that this includes the range that is being subtotalled. So, adding a row above or below won't change that formula.

You'll need to remove the subtotals and add them again.

In code

Sub Macro1()
Selection.RemoveSubtotal
Selection.Subtotal GroupBy:=1, _
Function:=xlSum, _
TotalList:=Array(2), _
Replace:=True, _
PageBreaks:=False, _
SummaryBelowData:=True
End Sub

This was generated by recording the manual actions.

Regards

Trevor


I have a spreadsheet that contains sales values for each month. As there can be more than one value for a month I am using the subtotal function to aggregate the sales to give me twelve values (one for each month). The spreadsheet is maintained on an ongoing basis, so new entries are added from time to time. The data range also has an auto filter so that I can select the records for any particular month if needed.

Currently, February only has one record. After applying the subtotal function to the range, I have added a second record for February, but the subtotal does not update. I am adding the second record by inserting a row and entering the values. However, the subtotal does not update itself irrespective of whether I add the row before or after the existing record.

If a month contains more than one record to begin with, I can insert a third row in between the two existing records and the subtotal will update automatically. The problem is specific to only having one record when the subtotal is set up in the first instance.

Example:

Month Sales($)
Jan 100
Jan 150
Jan 100
Jan Total 350
Feb 250
Feb Total 250
Mar 120
Mar 140
Mar Total 260

In the above example, The Feb Subtotal will not automatically update no matter where I insert a second record for Feb.

Help!

 

[Ronald Dodge]

One of the things that Excel's Subtotal function (Spreadsheet side from the
Data menu) is doing, setting up ranges for each of the sub totals, and given
how formula works with ranges, if you insert a row within the range, the
formula will adjust automatically, but if you don't insert within the range,
it won't automatically adjust, thus with your 1 row formula range, it would
not be possible for it to automatically adjust cause the row above and the
row below are both outside of the 1 row range. Given that, you will need to
manually adjust the subtotal formula to include both rows.

--
Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
I have a spreadsheet that contains sales values for each month. As there
can be more than one value for a month I am using the subtotal function to
aggregate the sales to give me twelve values (one for each month). The
spreadsheet is maintained on an ongoing basis, so new entries are added from
time to time. The data range also has an auto filter so that I can select
the records for any particular month if needed.

Currently, February only has one record. After applying the subtotal
function to the range, I have added a second record for February, but the
subtotal does not update. I am adding the second record by inserting a row
and entering the values. However, the subtotal does not update itself
irrespective of whether I add the row before or after the existing record.

If a month contains more than one record to begin with, I can insert a third
row in between the two existing records and the subtotal will update
automatically. The problem is specific to only having one record when the
subtotal is set up in the first instance.

Example:

Month Sales($)
Jan 100
Jan 150
Jan 100
Jan Total 350
Feb 250
Feb Total 250
Mar 120
Mar 140
Mar Total 260

In the above example, The Feb Subtotal will not automatically update no
matter where I insert a second record for Feb.

Help!