Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Checking data in 2 columns against a further 2 columns.... | Excel Worksheet Functions | |||
Sorting Data into columns without replacing the columns with data | New Users to Excel | |||
Help in code Steve G wrote to move data from 4 columns to 21 columns | Excel Programming | |||
Arrange data spanning 8 columns and 3 rows to 24 columns and 1 row | Excel Discussion (Misc queries) | |||
Transposing three columns into one row after manipulating data in columns | Excel Discussion (Misc queries) |