Help with Autosum macro in a dynamic range?

[Coyotex3]

Hello, so I will have a table like this that will be dynamic. One day it might have 100 rows and another day 10000. I would like for the macro to go from this:

Book1
	A	B	C	D	E
1	Report
2	8-Feb
3	User
4
5	Name	Order	Amount
6	James	10	100
7	James	15	150
8	James	15	150
9	James	15	150
10
11
12
13	John	20	200
14	John	20	200
15	John	20	200
16	John	20	200
17	John	20	200
18	John	20	200
19	John	20	200
20	John	20	200
21
22
23
24	Jane	20	200
25	Jane	20	200
26	Jane	20	200
27	Jane	20	200
28	Jane	20	200
29	Jane	20	200
30	Jane	20	200
31	Jane	20	200
32
33
34
35
36
37
38
Sheet1

To this:

Book1
	A	B	C	D
1	Report
2	8-Feb
3	User
4
5	Name	Order	Amount
6	James	10	100
7	James	15	150
8	James	15	150
9	James	15	150
10			550
11
12
13	John	20	200
14	John	20	200
15	John	20	200
16	John	20	200
17	John	20	200
18	John	20	200
19	John	20	200
20	John	20	200
21			1600
22
23
24	Jane	20	200
25	Jane	20	200
26	Jane	20	200
27	Jane	20	200
28	Jane	20	200
29	Jane	20	200
30	Jane	20	200
31	Jane	20	200
32			1600
33
34
Sheet1

Cell Formulas
Range	Formula
C10	C10	=SUM(C6:C9)
C21,C32	C21	=SUM(C13:C20)

Can this be done via VBA?

 

[Peter_SSs]

Try this

Sub AddTotals()
  Dim rA As Range
  
  For Each rA In Range("C1", Range("C" & Rows.Count).End(xlUp)).SpecialCells(xlConstants, xlNumbers).Areas
    rA.Cells(rA.Count + 1).Formula = "=sum(" & rA.Address & ")"
  Next rA
End Sub

 

[Coyotex3]

Try this

Sub AddTotals()
  Dim rA As Range
 
  For Each rA In Range("C1", Range("C" & Rows.Count).End(xlUp)).SpecialCells(xlConstants, xlNumbers).Areas
    rA.Cells(rA.Count + 1).Formula = "=sum(" & rA.Address & ")"
  Next rA
End Sub

This is absolutely brilliant!!! Thank you so much!

 

[Michael M]

Maybe this way....Ah, too quick for me @Peter_SSs

Sub MM1()
' ORIGINAL CODE BY hiker95
Dim Area As Range
For Each Area In Range("C2", Range("C" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants).Areas
  With Area
    Cells(.Row + .Rows.Count, 3).Value = Evaluate("=Sum(C" & .Row & ":C" & .Row + .Rows.Count - 1 & ")")
  End With
Next Area
End Sub

 

[Coyotex3]

Maybe this way....Ah, too quick for me @Peter_SSs

Sub MM1()
' ORIGINAL CODE BY hiker95
Dim Area As Range
For Each Area In Range("C2", Range("C" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants).Areas
  With Area
    Cells(.Row + .Rows.Count, 3).Value = Evaluate("=Sum(C" & .Row & ":C" & .Row + .Rows.Count - 1 & ")")
  End With
Next Area
End Sub

Michael thank you so much. This one works as well!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.