Excel - Adding a row outside of forumla range

Question

Currently I have a couple of SUM functions setup in my worksheet.

Lets use H2:H34 for my example: If I add a row within that range, the sum function will automatically adjust. The problem is when I add new rows, it is below the range (below H:33). In this case, I would just have to highlight the column and expand/drag the range to where it needs to be. There are a total of 8 columns that are doing a sum function. Is this only possible by using a macro?

=SUBTOTAL(3,H2:H34)

That was the first option I tried but it turns the total to "0". The sum formula sits in a H cell as well (H:35) — LOZ, Jun 22 '16 at 17:22

score 1 · Answer 1 · edited May 23 '17 at 12:22

you could use a named range like =sum(range1) and something like;

Select first empty cell in column F starting from row 1. (without using offset )

to find the first empty cell to change the reference for the named range.

Triggering it would be difficult... potentially could use on_save?

or option 2.... bit of a dodge, but can work....

you say your sum is in cell H35... maybe move it to h36, and create a dummy hidden row in 35 (0.1 row height). that way when you add a row, it is always above 35. Use sum H1:H35 and it will always auto update? its not a neat solution, but should work

Shai Rado · Answer 2 · 2016-06-22T17:40:47.550

1

Use the following code, you need to adjust column "H" to whatever column you need.

Dim sumtest    As Variant

sumstest = Application.WorksheetFunction.Subtotal(9, Range("H2:H" & ActiveSheet.Cells(ActiveSheet.Rows.Count, "H").End(xlUp).Row))

edited Jun 22 '16 at 17:40

answered Jun 22 '16 at 17:35

Shai Rado

33,032
6
29
51

As an OT: Integer is deprecated, you may google Integer vs Long to see why, just change Integer to Long – Sgdva Jun 22 '16 at 17:38
I would change it to a Double instead, as both Integer and Long will remove the decimal, if there are any. – Scott Craner Jun 22 '16 at 17:40

score 1 · Accepted Answer · answered Jun 22 '16 at 17:53

With your formula being the extent of the range minus one row we can use this:

=SUBTOTAL(3,H2:INDEX(H:H,ROW()-1))

Now as rows are added or deleted the reference will remain intact.

If there is a chance that row 2 would be deleted you will get #Ref errors. To avoid that you can use this formula:

=SUBTOTAL(3,INDEX(H:H,2):INDEX(H:H,ROW()-1))

Now there are no specific cell references and adding or deleting will not effect the formula and the range will be dynamic.

Golden. This solved my problem.Ty! Good to see there is a resolution outside of macros. — LOZ, Jun 22 '16 at 18:11

Excel - Adding a row outside of forumla range

3 Answers3