Sum columns of data in vba
 

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.

Sum columns of data in vba
 

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


wrote in message
...
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.

Sum columns of data in vba
 

On Mar 27, 3:13*pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
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

wrote in message

...



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.