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.

Answer 1

Here is a simple non-VBA Approach.

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

SNAPSHOT

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

Answer 2

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:

Answer 3

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.