Sum Multiple Columns with Macro
 

We have a workbook with seven columns of data and which
may have varying rows of data depending on the month's
data being captured.

A B C D E F G
1 xx 3
2 qq 4
3 dd 2
4 ww 5
5 ee 8
..
..
..

We are trying to include in our macro a routine that will
go to the first blank row below the data and enter the
word "Totals" in column A. Then we want to enter the sum
of each column (B through G) under each column on the same
blank row as the word "Totals." In the above example we
would enter "Totals" on row 6 in column A and then 5 would
be entered on row 6, column B. 12 would be in row, column
C. etc.

We got this far with the macro but couldn't figure out how
to get the sums entered.

Range("A1").Select
Nmrows = Range("A1").End(xlDown).Row
Cells(Nmrows + 1, 1) = "Totals"

TIA for your help.

Sum Multiple Columns with Macro
 

Here's one possibility:

Sub AddTotals()

NmRows = Range("A1").End(xlDown).Row
NmCols = Range("A1").CurrentRegion.Columns.Count

Cells(NmRows + 1, 1) = "Totals"
Cells(NmRows + 1, 2).Formula = "=SUM(" & Range(Cells(1, 2),
Cells(NmRows, 2)).Address(ColumnAbsolute:=False) & ")"
Cells(NmRows + 1, 2).AutoFill Destination:=Range(Cells(NmRows + 1, 2),
Cells(NmRows + 1, NmCols))

End Sub


--
_________________________
Robert Rosenberg
R-COR Consulting Services
Microsoft MVP - Excel
http://www.r-cor.com


"Ken" wrote in message
...
We have a workbook with seven columns of data and which
may have varying rows of data depending on the month's
data being captured.

A B C D E F G
1 xx 3
2 qq 4
3 dd 2
4 ww 5
5 ee 8
.
.
.

We are trying to include in our macro a routine that will
go to the first blank row below the data and enter the
word "Totals" in column A. Then we want to enter the sum
of each column (B through G) under each column on the same
blank row as the word "Totals." In the above example we
would enter "Totals" on row 6 in column A and then 5 would
be entered on row 6, column B. 12 would be in row, column
C. etc.

We got this far with the macro but couldn't figure out how
to get the sums entered.

Range("A1").Select
Nmrows = Range("A1").End(xlDown).Row
Cells(Nmrows + 1, 1) = "Totals"

TIA for your help.

Sum Multiple Columns with Macro
 

Here's a way. It looks for the last row from the bottom up, so if there's
more data below the total, you'd want to use your original code. Otherwise
it's sometimes more foolproof to go from the bottom up:

Sub test()

Dim total_row As Double

total_row = Range("A" & Rows.Count).End(xlUp).Row + 1 ' change to your
xlDown code if there's data below totals
Cells(total_row, 1) = "Totals"
Range("B" & total_row).Formula = "=sum(B1:B" & total_row - 1 & ")"
Range("C" & total_row, "G" & total_row).FormulaR1C1 = Range("B" &
total_row).FormulaR1C1

End Sub

hth,

Doug

"Ken" wrote in message
...
We have a workbook with seven columns of data and which
may have varying rows of data depending on the month's
data being captured.

A B C D E F G
1 xx 3
2 qq 4
3 dd 2
4 ww 5
5 ee 8
.
.
.

We are trying to include in our macro a routine that will
go to the first blank row below the data and enter the
word "Totals" in column A. Then we want to enter the sum
of each column (B through G) under each column on the same
blank row as the word "Totals." In the above example we
would enter "Totals" on row 6 in column A and then 5 would
be entered on row 6, column B. 12 would be in row, column
C. etc.

We got this far with the macro but couldn't figure out how
to get the sums entered.

Range("A1").Select
Nmrows = Range("A1").End(xlDown).Row
Cells(Nmrows + 1, 1) = "Totals"

TIA for your help.