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
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
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
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
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.
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.
