How to sum columns for a selected range using VBA?

Question

After I have selected a range containing numerical values, I want to, via VBA, input a =SUM formula at the bottom of each column, i.e. on the row after the last selected row. For each column it should sum all of the values in the corresponding column of the entire selection.

How can I do this?

Right now, I am using the code given by the macro recorder: ActiveCell.FormulaR1C1 = "=SUM(R[-10]C:R[-1]C)". The problem is when my range gets bigger than 10 rows it will not take the rows above the bottom 10 into consideration.

enter image description here

Jean-François Corbett · Accepted Answer · 2017-08-15 11:16:36Z

8

Here is a simple non-VBA Approach.

Select the cells where you want the sum and press Alt-=.

SNAPSHOT

enter image description here

And here is a one-line VBA code that does the same thing.

Sub AutoSum()
    '~~> After you select your range
    Application.CommandBars.ExecuteMso ("AutoSum")
End Sub

edited Aug 15, 2017 at 11:16

Jean-François Corbett

38.7k30 gold badges145 silver badges192 bronze badges

answered Mar 21, 2012 at 9:13

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jean-François Corbett · Accepted Answer · 2012-03-21 08:47:42Z

6

This works:

Sub MakeSums()
    Dim source As Range
    Dim iCol As Long
    Dim nCol As Long
    Dim nRow As Long

    Set source = Selection

    nCol = source.Columns.Count
    nRow = source.Rows.Count
    For iCol = 1 To nCol
        With source.Columns(iCol).Rows(nRow).Offset(1, 0)
            .FormulaR1C1 = "=SUM(R[-" & nRow & "]C:R[-1]C)"
            .Font.Bold = True
        End With
    Next iCol

End Sub

Example:

enter image description here

answered Mar 21, 2012 at 8:47

Jean-François Corbett

38.7k30 gold badges145 silver badges192 bronze badges

Comments

markblandford · Accepted Answer · 2012-03-21 09:01:56Z

1

You could also do something like this without VBA:

=SUM(OFFSET(INDIRECT(CELL("address")),1-ROW(),0,ROW()-1,1))

This will sum all cells above the cell in which the formula exists.

answered Mar 21, 2012 at 9:01

markblandford

3,2133 gold badges22 silver badges28 bronze badges

Comments

Rick Sable · Accepted Answer · 2021-09-14 14:47:53Z

1

Here is a quick trick to sum selected cells in vba code.

Private Sub Sum_Click()
    MsgBox Application.Sum(Range(Selection.Address))
End Sub

This is what I was searching for when I landed on this thread so maybe it will help someone.

answered Sep 14, 2021 at 14:47

Rick Sable

111 bronze badge

Collectives™ on Stack Overflow

How to sum columns for a selected range using VBA?

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related