Macro/VBA help. Insert Sum() to bottom of numbers, continuously in column, till end.

Kinsleigh

New Member
Joined
Apr 15, 2018
Messages
2
Hi Everyone,

I need some help to try to overcome this time consuming task in excel. I hope someone here can help.

I have a worksheet that I'm working on, which I have to insert a Sum() Function to the bottom of some numbers in a column to give me the total. (Im working on a single column)When I click the autosum() at the bottom of rows of numbers, Excel intelligently seem to know and auto sum all the numbers above and give me a Total value. Pretend this was 3 rows.

Repeat this process again, but the next time its 4 rows or 5 rows or x number of rows to autosum(). I have like 4000 rows and tonz of sum functions to insert. This is taking me along time to do. I created a macro, which seem promising, but failed, because the macro does not have the intelligent to autosum all the numbers above.

Basically, my macro is using relative Reference, and its. Control down, Control down, down, sum(), enter. end.

Again, the maceo does not have the intelligent to autosum all the numbers above.

Sub Macro11()
'
' Macro11 Macro
'


'
Selection.End(xlDown).Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-3]C:R[-1]C)"
ActiveCell.Offset(1, 0).Range("A1").Select
End Sub

Please help. Thanks.
Kinsleigh



1
2
3
6 is total




12
2
3
5
22 is total




5
5
5
5
5
25 is total



<tbody>
</tbody>



<tbody>
</tbody>
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Welcome to the MrExcel board!

I'm a little unsure about where your data is and where you want the formulas, but see if one of these heads you in the right direction. Test in a copy of your workbook.

If the numbers are in a single column (I've used B) and are 'constants' (that is, not the results of formulas) then try this
Rich (BB code):
Sub InsertSumFormulasInOneColumn()
  Dim rA As Range
  
  For Each rA In Columns("B").SpecialCells(xlConstants, xlNumbers).Areas
    rA.Cells(rA.Cells.Count + 1).Formula = "=SUM(" & rA.Address & ")"
  Next rA
End Sub

If each group of numbers is in a separate column (starting at column B (2) and ending wherever the last value in row 1 is), then try this one.
Rich (BB code):
Sub InsertSumFormulasInEachColumn()
  Dim i As Long
  
  For i = 2 To Cells(1, Columns.Count).End(xlToLeft).Column
    Cells(Rows.Count, i).End(xlUp).Offset(1).FormulaR1C1 = "=SUM(R1C:R[-1]C)"
  Next i
End Sub
 
Last edited:
Upvote 0
Welcome to the MrExcel board!

I'm a little unsure about where your data is and where you want the formulas, but see if one of these heads you in the right direction. Test in a copy of your workbook.

If the numbers are in a single column (I've used B) and are 'constants' (that is, not the results of formulas) then try this
Rich (BB code):
Sub InsertSumFormulasInOneColumn()
  Dim rA As Range
  
  For Each rA In Columns("B").SpecialCells(xlConstants, xlNumbers).Areas
    rA.Cells(rA.Cells.Count + 1).Formula = "=SUM(" & rA.Address & ")"
  Next rA
End Sub

If each group of numbers is in a separate column (starting at column B (2) and ending wherever the last value in row 1 is), then try this one.
Rich (BB code):
Sub InsertSumFormulasInEachColumn()
  Dim i As Long
  
  For i = 2 To Cells(1, Columns.Count).End(xlToLeft).Column
    Cells(Rows.Count, i).End(xlUp).Offset(1).FormulaR1C1 = "=SUM(R1C:R[-1]C)"
  Next i
End Sub

Oh my God. It worked. Your first code worked. Are you able to comment in your code and let me know what is happening. I need to learn VBA asap. This is priceless.
 
Upvote 0
Oh my God. It worked. Your first code worked. Are you able to comment in your code and let me know what is happening. I need to learn VBA asap. This is priceless.
Glad it worked for you.

There isn't much in the code to comment but below is an outline using the sample data shown here.

Excel Workbook
B
1Header
21
36
45
53
64
7
8
9
102
118
126
13
14
15
169
1756
1823
1998
2099
21
Sum Formulas 2




Code:
Columns("B").SpecialCells(xlConstants, xlNumbers).Areas
This looks for numbers (constants, not formulas) in column B and records as an "area" each contiguous group. So green, blue and yellow are 3 areas.

Code:
For Each rA In Columns("B").SpecialCells(xlConstants, xlNumbers).Areas
For each of those areas in turn..

Code:
rA.Cells(rA.Cells.Count + 1).Formula = "=SUM(" & rA.Address & ")"
Go to the cell immediately below the area and put a normal =SUM(xxx) formula where XXX is the address of the area ($B$2:$B$6 for the first one, $B$10:$B$12 for the second etc)

That's about it. :)
 
Last edited:
Upvote 0
This coding works nicely, thank you, but I'm interested in a more general case where it only sums up the values above the active cell exactly like Auto Sum does, how would the coding look like then? thanks for any help.
 
Upvote 0
I'm interested .. where it only sums up the values above the active cell ..
Above the active cell up to where? (eg row 2, until an empty cell or the top edge of the sheet is encountered, something else?)

BTW, using the active cell is usually a slower way of doing things. It may be that if you gave a fuller description of what you have, where, and exactly what you are trying to achieve, there may be a more efficient way than using the active cell.
 
Upvote 0
Hi Peter, what I'm interested is the vba equivalent of Excel's AutoSum feature ie: if I click on a random cell, say E22, then it will sum all values above E22 until the first blank cell and then put SUM(whatever the range is) inside cell E22... thanks again!
 
Upvote 0
Hi Peter, what I'm interested is the vba equivalent of Excel's AutoSum feature ie: if I click on a random cell, say E22, then it will sum all values above E22 until the first blank cell and then put SUM(whatever the range is) inside cell E22... thanks again!
Give this a try. It doesn't exactly match the Autosum behaviour but may suffice for you. If not, specific examples of data, location and expected result would help.

Code:
Sub InsertSUMformula()
  Dim fr As Long
  
  With ActiveCell
    Select Case .Row
      Case 1
        fr = 0
      Case 2
        fr = 1
      Case Else
        If IsEmpty(.Offset(-1).Value) Then
          fr = 0
        ElseIf IsEmpty(.Offset(-2).Value) Then
          fr = .Row - 1
        Else
          fr = .Offset(-1).End(xlUp).Row
        End If
    End Select
      If fr > 0 Then
        .FormulaR1C1 = "=SUM(R" & fr & "C:R[-1]C)"
      Else
        .Value = "Error"
      End If
  End With
End Sub
 
Last edited:
Upvote 0
Works like a charm! But why do you say it might not match Autosum's behavior exactly? (just curious).
 
Upvote 0
Example 1
In column E the only numbers are in E5:E7
You select E22
Autosum will suggest =SUM(E5:E21)
My code gives error because you said to add up to the first empty cell and that is E21 which does not contain a number.

Example 2
Only data on the sheet is numbers in E5:E7 and B22:D22
You select E22
Autosum will suggest =SUM(B22:D22)
but you want to sum vertically, not horizontally.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,981
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top