Macro or formula to merge rows if has a merged cell
I am trying to work out a formula or macro that would pickup to create 1 line when rows share a merged cell (see example in Column N). All other details are the same except columns L & N. Columns L & N would need to be added together to create the total value as well.
Although not all orders have a merged cell as well, some are just single lines
Columns A-K and O-Y are all the same data. Columns L & N have different data that need to be added together to total the amount whilst Column N is a merged cell across the rows with 1 number.
Although other lines will have all the columns with different data which means it needs to pick up the row on its own
Click on image for larger view
microsoft-excel worksheet-function macros
edited Jan 29 at 10:16
fixer1234
19k144982
asked Jan 29 at 9:29
TabbyTabby
62
add a comment |
I am trying to work out a formula or macro that would pickup to create 1 line when rows share a merged cell (see example in Column N). All other details are the same except columns L & N. Columns L & N would need to be added together to create the total value as well.
Although not all orders have a merged cell as well, some are just single lines
Columns A-K and O-Y are all the same data. Columns L & N have different data that need to be added together to total the amount whilst Column N is a merged cell across the rows with 1 number.
Although other lines will have all the columns with different data which means it needs to pick up the row on its own
Click on image for larger view
microsoft-excel worksheet-function macros
edited Jan 29 at 10:16
fixer1234
19k144982
asked Jan 29 at 9:29
TabbyTabby
62
I did that but its still not in it
– Tabby
Jan 29 at 9:58
sorry just realised what you meant. I don't have an account there
– Tabby
Jan 29 at 9:59
The link was hiding in the question. :-)
– fixer1234
Jan 29 at 10:17
1
Will column N be the only place where there can potentially be merged cells, or can they be in other columns, also?
– fixer1234
Jan 29 at 10:24
Only column N would have potential merged cell
– Tabby
Jan 29 at 11:20
add a comment |
I am trying to work out a formula or macro that would pickup to create 1 line when rows share a merged cell (see example in Column N). All other details are the same except columns L & N. Columns L & N would need to be added together to create the total value as well.
Although not all orders have a merged cell as well, some are just single lines
Columns A-K and O-Y are all the same data. Columns L & N have different data that need to be added together to total the amount whilst Column N is a merged cell across the rows with 1 number.
Although other lines will have all the columns with different data which means it needs to pick up the row on its own
Click on image for larger view
microsoft-excel worksheet-function macros
edited Jan 29 at 10:16
fixer1234
19k144982
asked Jan 29 at 9:29
TabbyTabby
62
I am trying to work out a formula or macro that would pickup to create 1 line when rows share a merged cell (see example in Column N). All other details are the same except columns L & N. Columns L & N would need to be added together to create the total value as well.
Although not all orders have a merged cell as well, some are just single lines
Columns A-K and O-Y are all the same data. Columns L & N have different data that need to be added together to total the amount whilst Column N is a merged cell across the rows with 1 number.
Although other lines will have all the columns with different data which means it needs to pick up the row on its own
Click on image for larger view
microsoft-excel worksheet-function macros
microsoft-excel worksheet-function macros
edited Jan 29 at 10:16
fixer1234
19k144982
asked Jan 29 at 9:29
TabbyTabby
62
edited Jan 29 at 10:16
fixer1234
19k144982
asked Jan 29 at 9:29
TabbyTabby
62
edited Jan 29 at 10:16
fixer1234
19k144982
edited Jan 29 at 10:16
fixer1234
19k144982
edited Jan 29 at 10:16
fixer1234
19k144982
19k144982
asked Jan 29 at 9:29
TabbyTabby
62
asked Jan 29 at 9:29
TabbyTabby
62
asked Jan 29 at 9:29
TabbyTabby
62
62
I did that but its still not in it
– Tabby
Jan 29 at 9:58
sorry just realised what you meant. I don't have an account there
– Tabby
Jan 29 at 9:59
The link was hiding in the question. :-)
– fixer1234
Jan 29 at 10:17
1
Will column N be the only place where there can potentially be merged cells, or can they be in other columns, also?
– fixer1234
Jan 29 at 10:24
Only column N would have potential merged cell
– Tabby
Jan 29 at 11:20
add a comment |
I did that but its still not in it
– Tabby
Jan 29 at 9:58
sorry just realised what you meant. I don't have an account there
– Tabby
Jan 29 at 9:59
The link was hiding in the question. :-)
– fixer1234
Jan 29 at 10:17
1
Will column N be the only place where there can potentially be merged cells, or can they be in other columns, also?
– fixer1234
Jan 29 at 10:24
Only column N would have potential merged cell
– Tabby
Jan 29 at 11:20
I did that but its still not in it
– Tabby
Jan 29 at 9:58
I did that but its still not in it
– Tabby
Jan 29 at 9:58
sorry just realised what you meant. I don't have an account there
– Tabby
Jan 29 at 9:59
sorry just realised what you meant. I don't have an account there
– Tabby
Jan 29 at 9:59
The link was hiding in the question. :-)
– fixer1234
Jan 29 at 10:17
The link was hiding in the question. :-)
– fixer1234
Jan 29 at 10:17
1
1
Will column N be the only place where there can potentially be merged cells, or can they be in other columns, also?
– fixer1234
Jan 29 at 10:24
Will column N be the only place where there can potentially be merged cells, or can they be in other columns, also?
– fixer1234
Jan 29 at 10:24
Only column N would have potential merged cell
– Tabby
Jan 29 at 11:20
Only column N would have potential merged cell
– Tabby
Jan 29 at 11:20
add a comment |
1 Answer
1
active
oldest
votes
Public Function SumByMerge(ColOffset As Integer) As Double
Dim OneCell As Range
SumByMerge = 0
For Each OneCell In Range(Range(Application.Caller.Address).MergeArea.Address)
SumByMerge = SumByMerge + OneCell.Offset(0, ColOffset).Value
Next
End Function
and then
N2=SumByMerge(-1)
Remember - you must add checking that source cell values are numeric, and that the calculations do not go beyond the worksheet boundaries.
PS. If you enter the formula to a cell (N2), and THEN select and merge a region (N2:N3), Excel will NOT recalculate the function - it cannot detect that indirect source range was changed. Recalculate it manually.
answered Jan 29 at 10:57
AkinaAkina
1,36729
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
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
});
}
});
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%2fsuperuser.com%2fquestions%2f1399567%2fmacro-or-formula-to-merge-rows-if-has-a-merged-cell%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
Public Function SumByMerge(ColOffset As Integer) As Double
Dim OneCell As Range
SumByMerge = 0
For Each OneCell In Range(Range(Application.Caller.Address).MergeArea.Address)
SumByMerge = SumByMerge + OneCell.Offset(0, ColOffset).Value
Next
End Function
and then
N2=SumByMerge(-1)
Remember - you must add checking that source cell values are numeric, and that the calculations do not go beyond the worksheet boundaries.
PS. If you enter the formula to a cell (N2), and THEN select and merge a region (N2:N3), Excel will NOT recalculate the function - it cannot detect that indirect source range was changed. Recalculate it manually.
answered Jan 29 at 10:57
AkinaAkina
1,36729
add a comment |
Public Function SumByMerge(ColOffset As Integer) As Double
Dim OneCell As Range
SumByMerge = 0
For Each OneCell In Range(Range(Application.Caller.Address).MergeArea.Address)
SumByMerge = SumByMerge + OneCell.Offset(0, ColOffset).Value
Next
End Function
and then
N2=SumByMerge(-1)
Remember - you must add checking that source cell values are numeric, and that the calculations do not go beyond the worksheet boundaries.
PS. If you enter the formula to a cell (N2), and THEN select and merge a region (N2:N3), Excel will NOT recalculate the function - it cannot detect that indirect source range was changed. Recalculate it manually.
answered Jan 29 at 10:57
AkinaAkina
1,36729
add a comment |
Public Function SumByMerge(ColOffset As Integer) As Double
Dim OneCell As Range
SumByMerge = 0
For Each OneCell In Range(Range(Application.Caller.Address).MergeArea.Address)
SumByMerge = SumByMerge + OneCell.Offset(0, ColOffset).Value
Next
End Function
and then
N2=SumByMerge(-1)
Remember - you must add checking that source cell values are numeric, and that the calculations do not go beyond the worksheet boundaries.
PS. If you enter the formula to a cell (N2), and THEN select and merge a region (N2:N3), Excel will NOT recalculate the function - it cannot detect that indirect source range was changed. Recalculate it manually.
answered Jan 29 at 10:57
AkinaAkina
1,36729
Public Function SumByMerge(ColOffset As Integer) As Double
Dim OneCell As Range
SumByMerge = 0
For Each OneCell In Range(Range(Application.Caller.Address).MergeArea.Address)
SumByMerge = SumByMerge + OneCell.Offset(0, ColOffset).Value
Next
End Function
and then
N2=SumByMerge(-1)
Remember - you must add checking that source cell values are numeric, and that the calculations do not go beyond the worksheet boundaries.
PS. If you enter the formula to a cell (N2), and THEN select and merge a region (N2:N3), Excel will NOT recalculate the function - it cannot detect that indirect source range was changed. Recalculate it manually.
answered Jan 29 at 10:57
AkinaAkina
1,36729
edited Jan 29 at 11:07
answered Jan 29 at 10:57
AkinaAkina
1,36729
answered Jan 29 at 10:57
AkinaAkina
1,36729
answered Jan 29 at 10:57
AkinaAkina
1,36729
1,36729
add a comment |
add a comment |
Thanks for contributing an answer to Super User!
- 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.
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%2fsuperuser.com%2fquestions%2f1399567%2fmacro-or-formula-to-merge-rows-if-has-a-merged-cell%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
I did that but its still not in it
– Tabby
Jan 29 at 9:58
sorry just realised what you meant. I don't have an account there
– Tabby
Jan 29 at 9:59
The link was hiding in the question. :-)
– fixer1234
Jan 29 at 10:17
1
Will column N be the only place where there can potentially be merged cells, or can they be in other columns, also?
– fixer1234
Jan 29 at 10:24
Only column N would have potential merged cell
– Tabby
Jan 29 at 11:20