Column totals using macro
Closed
exhale
Posts
5
Registration date
Friday January 3, 2014
Status
Member
Last seen
January 3, 2014
-
Jan 3, 2014 at 02:22 AM
exhale Posts 5 Registration date Friday January 3, 2014 Status Member Last seen January 3, 2014 - Jan 3, 2014 at 11:51 PM
exhale Posts 5 Registration date Friday January 3, 2014 Status Member Last seen January 3, 2014 - Jan 3, 2014 at 11:51 PM
Related:
- Column totals using macro
- Excel online macro - Guide
- How to delete column in word - Guide
- Excel run macro on open - Guide
- Tweetdeck expand column - Guide
- Excel count occurrences of string in column - Guide
9 responses
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Jan 3, 2014 at 03:02 AM
Jan 3, 2014 at 03:02 AM
first row is headings srow
macro can be
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
exhale
Posts
5
Registration date
Friday January 3, 2014
Status
Member
Last seen
January 3, 2014
Jan 3, 2014 at 04:45 AM
Jan 3, 2014 at 04:45 AM
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
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
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Jan 3, 2014 at 06:43 AM
Jan 3, 2014 at 06:43 AM
try this
AFTER understanding the macro remove comment statement which start with single apostrophe
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
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Jan 3, 2014 at 06:49 AM
Jan 3, 2014 at 06:49 AM
that macro is wrong. wait for some time
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Jan 3, 2014 at 07:10 AM
Jan 3, 2014 at 07:10 AM
DISCARD PREVIOUS MACRO
try this
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
exhale
Posts
5
Registration date
Friday January 3, 2014
Status
Member
Last seen
January 3, 2014
Jan 3, 2014 at 07:23 AM
Jan 3, 2014 at 07:23 AM
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.
exhale
Posts
5
Registration date
Friday January 3, 2014
Status
Member
Last seen
January 3, 2014
Jan 3, 2014 at 08:53 AM
Jan 3, 2014 at 08:53 AM
Will you pls put apostrophes in your code to explain the steps? it works but I don't understand it fully
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Jan 3, 2014 at 09:58 PM
Jan 3, 2014 at 09:58 PM
"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.
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.
exhale
Posts
5
Registration date
Friday January 3, 2014
Status
Member
Last seen
January 3, 2014
Jan 3, 2014 at 11:51 PM
Jan 3, 2014 at 11:51 PM
Ok thanks
