How to sum columns for a selected range using VBA?

Question

After I have selected a range containing numerical values, I want to enter through VBA the formula =SUM at the bottom of each column, that is, in the row after the last row selected. For each column, it must summarize all the values ​​in the corresponding column of the entire selection.

How can i do this?

I am currently using the code specified by the macro recorder: ActiveCell.FormulaR1C1 = "=SUM(R[-10]C:R[-1]C)" . The problem is that when my range becomes more than 10 rows, it will not accept rows above bottom 10.

Answer 1

It 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 2

Here is a simple approach different from VBA.

Select the cells in which you want to receive the amount, and press Alt - = .

Snapshot

And here is the single line VBA code that does the same.

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

Answer 3

You can also do something like this without VBA:

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

This sums all the cells above the cell in which the formula exists.