Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

Column totals using macro [Closed]

Report
Posts
5
Registration date
Friday January 3, 2014
Status
Member
Last seen
January 3, 2014
 -
Posts
5
Registration date
Friday January 3, 2014
Status
Member
Last seen
January 3, 2014
 -
need help , how do I add sum totals at the bottom of columns of varying lengths using macros. eg I have data in columns a to f, I want columns c to f to be summed up and the sum to be at the bottom of the columns in one row as per below example. remember the columns vary in length but column A to C are always the longest in length.

A B C D E F
405104680 201304 527.07 75.484
405104680 201305 527.07 73.468
405104680 201306 527.07 345 74.12 5544
405104680 201307 0 0
405104680 201308 0 0
405104680 201309 0 78 0
405104680 201312 527.07 3527.156
405104680 201401 527.07

sum 2,635.35 423.00 3,750.23 5,544.00
See more
Related:

9 replies

Answer 1 / 9
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
 790
first row is headings srow

macro can be 

Sub test()
Dim r As Range, lastrow As Integer, c As Range
lastrow = Range("A1").CurrentRegion.SpecialCells(xlCellTypeLastCell).Row

Set r = Range("C1:F1")
For Each c In r
Cells(lastrow + 1, c.Column) = WorksheetFunction.Sum(Range(Cells(2, c.Column), Cells(lastrow, c.Column)))
Next c


End Sub
Answer 2 / 9
Posts
5
Registration date
Friday January 3, 2014
Status
Member
Last seen
January 3, 2014
thanks alot, the code works but 1st row is not a headings so i'd like the sum to include 1st row. I also have a few requests in addtions;

1. may I see the actual formula in the totals row and not just the number?
2. i'd like the total's row to be formatted, number to be comma separated eg 22,539.45 and
3. totals(sum) row C to F to have borders, 1 line on top 2 lines at bottom.

i used code below but it only sums one column & formats one cell in which there's a total. I'm new to macros, your help is much appreciated

Range("C1048576").End(xlUp).Offset(2, 0).Select
Selection.FormulaR1C1 = "=SUM(R1c3:r[-2]C)"
Selection.Style = "comma"
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlDouble
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThick
End With
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Answer 3 / 9
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
 790
try this 

Sub testone()
Dim r As Range
'Range("C1048576").End(xlUp).Offset(2, 0).Select
Set r = Cells(Rows.Count, "C").End(xlUp).Offset(2, 0)
'Selection.FormulaR1C1 = "=SUM(R1c3:r[-2]C)"
r.FormulaR1C1 = "=SUM(R1c3:r[-2]C)"
'Selection.Style = "comma"
'Selection.Borders(xlDiagonalDown).LineStyle = xlNone
'Selection.Borders(xlDiagonalUp).LineStyle = xlNone
'Selection.Borders(xlEdgeLeft).LineStyle = xlNone
'With Selection.Borders(xlEdgeTop)
'.LineStyle = xlContinuous
'.ColorIndex = 0
'.TintAndShade = 0
'.Weight = xlThin
'End With
'With Selection.Borders(xlEdgeBottom)
'.LineStyle = xlDouble
'.ColorIndex = 0
'.TintAndShade = 0
'.Weight = xlThick
r.NumberFormat = "0,000.00"
r.Copy Range(r.Offset(0, 1), r.Offset(0, 3))

End Sub

AFTER understanding the macro remove comment statement which start with single apostrophe
Answer 4 / 9
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
 790
that macro is wrong. wait for some time
Answer 5 / 9
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
 790
DISCARD PREVIOUS MACRO

try this 

Sub testTWO()
Dim r As Range, lastrow As Integer, c As Range, myformula
lastrow = Range("A1").End(xlDown).Row

Set r = Cells(lastrow + 2, "C")
Do
r.Formula = "=sum(" & r.Offset(-2, 0).Address & ":" & Cells(1, r.Column).Address & ")"
r.NumberFormat = "#,##0.00"
Set r = r.Offset(0, 1)
If r.Column > Range("F1").Column Then Exit Do
Loop


End Sub
Answer 6 / 9
Posts
5
Registration date
Friday January 3, 2014
Status
Member
Last seen
January 3, 2014
Works like a charm, thanks a lot. just one more thing, how do I add borders to the results? preferably top and double bottom borders. you've been a great help.
Answer 7 / 9
Posts
5
Registration date
Friday January 3, 2014
Status
Member
Last seen
January 3, 2014
Will you pls put apostrophes in your code to explain the steps? it works but I don't understand it fully
Answer 8 / 9
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
 790
"add borders"

best way are you having version excel2007 orhigher
then click DEVeLOPER ribbon and click RECORD MACRO on the extreme left
a macro name is suggested accept and note that name

now do whatever you want to do regarding border

use that macro. Perhaps macro has to be tweaked but this may not be necessary. if there is problem revert with copy of the recorded macro.

regarding code
r is a variable which signifies the 2 rows below last row and cell C in that row.

then the formula
r is now changed to result row col D
formula is redone
etc till you reach end of the columns

that numberformat is formatting number with comma at thousand points and two decimal points.
Answer 9 / 9
Posts
5
Registration date
Friday January 3, 2014
Status
Member
Last seen
January 3, 2014
Ok thanks
Ask a question