Merge and Sum totals in Excel VBA
0
I have a table in Excel and I need it to sum the totals of cell B and return the value on the cell after the last value. The following code does that.
Sub Total()
Range("B2").End(xlDown).Offset(1, 0) = _
"=sum($B$2:" & Range("B2").End(xlDown).Address & ")"
End Sub
I need it to merge the cells from B to H, format the text to bold and change the color and add the text "Total", like this
excel vba merge totals
asked Nov 25 '18 at 23:53
CaracksCaracks
96
add a comment |
0
I have a table in Excel and I need it to sum the totals of cell B and return the value on the cell after the last value. The following code does that.
Sub Total()
Range("B2").End(xlDown).Offset(1, 0) = _
"=sum($B$2:" & Range("B2").End(xlDown).Address & ")"
End Sub
I need it to merge the cells from B to H, format the text to bold and change the color and add the text "Total", like this
excel vba merge totals
asked Nov 25 '18 at 23:53
CaracksCaracks
96
You have a table, you want to sum the totals of Column B and you have a routine that does this. What is your specific problem? The rest of your post asks for code that does something different, and Stack Overflow is not a code-writing service. What have you tried to solve your second problem?
– AJD
Nov 26 '18 at 6:16
add a comment |
0
0
0
I have a table in Excel and I need it to sum the totals of cell B and return the value on the cell after the last value. The following code does that.
Sub Total()
Range("B2").End(xlDown).Offset(1, 0) = _
"=sum($B$2:" & Range("B2").End(xlDown).Address & ")"
End Sub
I need it to merge the cells from B to H, format the text to bold and change the color and add the text "Total", like this
excel vba merge totals
asked Nov 25 '18 at 23:53
CaracksCaracks
96
I have a table in Excel and I need it to sum the totals of cell B and return the value on the cell after the last value. The following code does that.
Sub Total()
Range("B2").End(xlDown).Offset(1, 0) = _
"=sum($B$2:" & Range("B2").End(xlDown).Address & ")"
End Sub
I need it to merge the cells from B to H, format the text to bold and change the color and add the text "Total", like this
excel vba merge totals
excel vba merge totals
asked Nov 25 '18 at 23:53
CaracksCaracks
96
asked Nov 25 '18 at 23:53
CaracksCaracks
96
asked Nov 25 '18 at 23:53
CaracksCaracks
96
asked Nov 25 '18 at 23:53
CaracksCaracks
96
asked Nov 25 '18 at 23:53
CaracksCaracks
96
96
You have a table, you want to sum the totals of Column B and you have a routine that does this. What is your specific problem? The rest of your post asks for code that does something different, and Stack Overflow is not a code-writing service. What have you tried to solve your second problem?
– AJD
Nov 26 '18 at 6:16
add a comment |
You have a table, you want to sum the totals of Column B and you have a routine that does this. What is your specific problem? The rest of your post asks for code that does something different, and Stack Overflow is not a code-writing service. What have you tried to solve your second problem?
– AJD
Nov 26 '18 at 6:16
You have a table, you want to sum the totals of Column B and you have a routine that does this. What is your specific problem? The rest of your post asks for code that does something different, and Stack Overflow is not a code-writing service. What have you tried to solve your second problem?
– AJD
Nov 26 '18 at 6:16
You have a table, you want to sum the totals of Column B and you have a routine that does this. What is your specific problem? The rest of your post asks for code that does something different, and Stack Overflow is not a code-writing service. What have you tried to solve your second problem?
– AJD
Nov 26 '18 at 6:16
add a comment |
1 Answer
1
active
oldest
votes
0
Sub Total()
Dim l As Long
l = Cells(Rows.Count, "A").End(xlUp).Row
Range("B" & l + 1) = "=sum(B2:B" & l & ")"
With Range("A" & l + 1)
.FormulaR1C1 = "Total"
.Font.Bold = True
End With
With Range("B" & l + 1 & ":H" & l + 1)
.MergeCells = True
.Font.Bold = True
.HorizontalAlignment = xlCenter
End With
With Range("A" & l + 1 & ":H" & l + 1).Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.149998474074526
.PatternTintAndShade = 0
End With
End Sub
answered Nov 26 '18 at 0:13
Michal RosaMichal Rosa
1,3361815
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53473198%2fmerge-and-sum-totals-in-excel-vba%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
Sub Total()
Dim l As Long
l = Cells(Rows.Count, "A").End(xlUp).Row
Range("B" & l + 1) = "=sum(B2:B" & l & ")"
With Range("A" & l + 1)
.FormulaR1C1 = "Total"
.Font.Bold = True
End With
With Range("B" & l + 1 & ":H" & l + 1)
.MergeCells = True
.Font.Bold = True
.HorizontalAlignment = xlCenter
End With
With Range("A" & l + 1 & ":H" & l + 1).Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.149998474074526
.PatternTintAndShade = 0
End With
End Sub
answered Nov 26 '18 at 0:13
Michal RosaMichal Rosa
1,3361815
add a comment |
0
Sub Total()
Dim l As Long
l = Cells(Rows.Count, "A").End(xlUp).Row
Range("B" & l + 1) = "=sum(B2:B" & l & ")"
With Range("A" & l + 1)
.FormulaR1C1 = "Total"
.Font.Bold = True
End With
With Range("B" & l + 1 & ":H" & l + 1)
.MergeCells = True
.Font.Bold = True
.HorizontalAlignment = xlCenter
End With
With Range("A" & l + 1 & ":H" & l + 1).Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.149998474074526
.PatternTintAndShade = 0
End With
End Sub
answered Nov 26 '18 at 0:13
Michal RosaMichal Rosa
1,3361815
add a comment |
0
0
0
Sub Total()
Dim l As Long
l = Cells(Rows.Count, "A").End(xlUp).Row
Range("B" & l + 1) = "=sum(B2:B" & l & ")"
With Range("A" & l + 1)
.FormulaR1C1 = "Total"
.Font.Bold = True
End With
With Range("B" & l + 1 & ":H" & l + 1)
.MergeCells = True
.Font.Bold = True
.HorizontalAlignment = xlCenter
End With
With Range("A" & l + 1 & ":H" & l + 1).Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.149998474074526
.PatternTintAndShade = 0
End With
End Sub
answered Nov 26 '18 at 0:13
Michal RosaMichal Rosa
1,3361815
Sub Total()
Dim l As Long
l = Cells(Rows.Count, "A").End(xlUp).Row
Range("B" & l + 1) = "=sum(B2:B" & l & ")"
With Range("A" & l + 1)
.FormulaR1C1 = "Total"
.Font.Bold = True
End With
With Range("B" & l + 1 & ":H" & l + 1)
.MergeCells = True
.Font.Bold = True
.HorizontalAlignment = xlCenter
End With
With Range("A" & l + 1 & ":H" & l + 1).Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.149998474074526
.PatternTintAndShade = 0
End With
End Sub
answered Nov 26 '18 at 0:13
Michal RosaMichal Rosa
1,3361815
answered Nov 26 '18 at 0:13
Michal RosaMichal Rosa
1,3361815
answered Nov 26 '18 at 0:13
Michal RosaMichal Rosa
1,3361815
answered Nov 26 '18 at 0:13
Michal RosaMichal Rosa
1,3361815
1,3361815
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53473198%2fmerge-and-sum-totals-in-excel-vba%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
You have a table, you want to sum the totals of Column B and you have a routine that does this. What is your specific problem? The rest of your post asks for code that does something different, and Stack Overflow is not a code-writing service. What have you tried to solve your second problem?
– AJD
Nov 26 '18 at 6:16