Sum columns of data in vba

[gi_stewart]

Hi,

I had a table of data, starting on row 6, with 4 columns and was using
this code to sum each column in a total row:

For i = 1 To 4
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=SUM(R6C:R[-1]C)"
Next i

I now have 4 more tables on the same worksheet, starting on rows that
can vary. Is there a way of modifying the above code to enter total
rows for each of the new tables? Thanks.

 

[Bernie Deitrick]

If you have tables running down some columns, with blank rows between them, and they are constant
values, then you could use this, for columns B to E

Sub Macro1()
Dim myA As Range

For Each myA In Columns("B:E").SpecialCells(xlCellTypeConstants, 23).Areas
myA.Cells(1, 1).Offset(myA.Rows.Count).Resize(1, myA.Columns.Count).Formula = _
"=SUM(" & myA.Columns(1).Address(False, False) & ")"
Next myA
End Sub


HTH,
Bernie
MS Excel MVP

 

[gi_stewart]

If you have tables running down somecolumns, with blank rows between them, and they are constant
values, then you could use this, forcolumnsB to E

Sub Macro1()
Dim myA As Range

For Each myA InColumns("B:E").SpecialCells(xlCellTypeConstants, 23).Areas
myA.Cells(1, 1).Offset(myA.Rows.Count).Resize(1, myA.Columns.Count).Formula = _
"=SUM(" & myA.Columns(1).Address(False, False) & ")"
Next myA
End Sub

HTH,
Bernie
MS Excel MVP

Hi,

I had a table of data, starting on row 6, with 4columnsand was using
this code tosumeach column in a total row:

For i = 1 To 4
   ActiveCell.Offset(0, 1).Select
   ActiveCell.FormulaR1C1 = "=SUM(R6C:R[-1]C)"
   Next i

I now have 4 more tables on the same worksheet, starting on rows that
can vary.  Is there a way of modifying the above code to enter total
rows for each of the new tables?  Thanks.- Hide quoted text -

- Show quoted text -

Thanks, Bernie, this worked great.