Setting Row Height via Excel VBA with merged cells and word wrap
3
What is the Excel VBA code that will set the correct (all characters visible and not excess space) row height with merged cells and word wrap on. My font is Calibri 11.
Is there some simple relationship between font size, column size and row height with word wrap on?
microsoft-excel-2013
edited Feb 15 '18 at 0:06
K.Dᴀᴠɪs
376419
asked Mar 5 '17 at 17:05
Ed HoldenEd Holden
164
add a comment |
3
What is the Excel VBA code that will set the correct (all characters visible and not excess space) row height with merged cells and word wrap on. My font is Calibri 11.
Is there some simple relationship between font size, column size and row height with word wrap on?
microsoft-excel-2013
edited Feb 15 '18 at 0:06
K.Dᴀᴠɪs
376419
asked Mar 5 '17 at 17:05
Ed HoldenEd Holden
164
Please note that superuser.com is not a free script/code writing service. If you tell us what you have tried so far (include the scripts/code you are already using) and where you are stuck then we can try to help with specific problems. You should also read How do I ask a good question?.
– DavidPostill♦
Mar 5 '17 at 17:29
add a comment |
3
3
3
What is the Excel VBA code that will set the correct (all characters visible and not excess space) row height with merged cells and word wrap on. My font is Calibri 11.
Is there some simple relationship between font size, column size and row height with word wrap on?
microsoft-excel-2013
edited Feb 15 '18 at 0:06
K.Dᴀᴠɪs
376419
asked Mar 5 '17 at 17:05
Ed HoldenEd Holden
164
What is the Excel VBA code that will set the correct (all characters visible and not excess space) row height with merged cells and word wrap on. My font is Calibri 11.
Is there some simple relationship between font size, column size and row height with word wrap on?
microsoft-excel-2013
microsoft-excel-2013
edited Feb 15 '18 at 0:06
K.Dᴀᴠɪs
376419
asked Mar 5 '17 at 17:05
Ed HoldenEd Holden
164
edited Feb 15 '18 at 0:06
K.Dᴀᴠɪs
376419
asked Mar 5 '17 at 17:05
Ed HoldenEd Holden
164
edited Feb 15 '18 at 0:06
K.Dᴀᴠɪs
376419
edited Feb 15 '18 at 0:06
K.Dᴀᴠɪs
376419
edited Feb 15 '18 at 0:06
K.Dᴀᴠɪs
376419
376419
asked Mar 5 '17 at 17:05
Ed HoldenEd Holden
164
asked Mar 5 '17 at 17:05
Ed HoldenEd Holden
164
asked Mar 5 '17 at 17:05
Ed HoldenEd Holden
164
164
Please note that superuser.com is not a free script/code writing service. If you tell us what you have tried so far (include the scripts/code you are already using) and where you are stuck then we can try to help with specific problems. You should also read How do I ask a good question?.
– DavidPostill♦
Mar 5 '17 at 17:29
add a comment |
Please note that superuser.com is not a free script/code writing service. If you tell us what you have tried so far (include the scripts/code you are already using) and where you are stuck then we can try to help with specific problems. You should also read How do I ask a good question?.
– DavidPostill♦
Mar 5 '17 at 17:29
Please note that superuser.com is not a free script/code writing service. If you tell us what you have tried so far (include the scripts/code you are already using) and where you are stuck then we can try to help with specific problems. You should also read How do I ask a good question?.
– DavidPostill♦
Mar 5 '17 at 17:29
Please note that superuser.com is not a free script/code writing service. If you tell us what you have tried so far (include the scripts/code you are already using) and where you are stuck then we can try to help with specific problems. You should also read How do I ask a good question?.
– DavidPostill♦
Mar 5 '17 at 17:29
add a comment |
1 Answer
1
active
oldest
votes
0
Moved answer from OP's question:
OrigRowHeight = SafeRange.RowHeight
OrigColWidth = SafeRange.ColumnWidth
CurRow = ActiveCell.Row
CurCol = ActiveCell.Column
NumMergeCols = ActiveCell.MergeArea.Count
LastCol = CurCol + NumMergeCols - 1
For i = CurCol To LastCol
CombinedColWidth = CombinedColWidth + Cells(CurRow, i).ColumnWidth
Next i
' Most of the following code came from Superuser user6261023 (My Thanks)
With SafeSheet.Range(SafeRange.Address)
TargetRange.Copy
.PasteSpecial xlPasteAll
.UnMerge
.ColumnWidth = CombinedColWidth
.Value = TargetRange.Value
.EntireRow.AutoFit
NeededRowHeight = 1.05 * .RowHeight / TargetRange.MergeArea.Rows.Count
.ClearContents
.ClearFormats
.RowHeight = OrigRowHeight
.ColumnWidth = OrigColWidth
End With
'Return NeededRowHeight
NewRowHeight = NeededRowHeight
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
});
}
});
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%2fsuperuser.com%2fquestions%2f1185484%2fsetting-row-height-via-excel-vba-with-merged-cells-and-word-wrap%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
Moved answer from OP's question:
OrigRowHeight = SafeRange.RowHeight
OrigColWidth = SafeRange.ColumnWidth
CurRow = ActiveCell.Row
CurCol = ActiveCell.Column
NumMergeCols = ActiveCell.MergeArea.Count
LastCol = CurCol + NumMergeCols - 1
For i = CurCol To LastCol
CombinedColWidth = CombinedColWidth + Cells(CurRow, i).ColumnWidth
Next i
' Most of the following code came from Superuser user6261023 (My Thanks)
With SafeSheet.Range(SafeRange.Address)
TargetRange.Copy
.PasteSpecial xlPasteAll
.UnMerge
.ColumnWidth = CombinedColWidth
.Value = TargetRange.Value
.EntireRow.AutoFit
NeededRowHeight = 1.05 * .RowHeight / TargetRange.MergeArea.Rows.Count
.ClearContents
.ClearFormats
.RowHeight = OrigRowHeight
.ColumnWidth = OrigColWidth
End With
'Return NeededRowHeight
NewRowHeight = NeededRowHeight
add a comment |
0
Moved answer from OP's question:
OrigRowHeight = SafeRange.RowHeight
OrigColWidth = SafeRange.ColumnWidth
CurRow = ActiveCell.Row
CurCol = ActiveCell.Column
NumMergeCols = ActiveCell.MergeArea.Count
LastCol = CurCol + NumMergeCols - 1
For i = CurCol To LastCol
CombinedColWidth = CombinedColWidth + Cells(CurRow, i).ColumnWidth
Next i
' Most of the following code came from Superuser user6261023 (My Thanks)
With SafeSheet.Range(SafeRange.Address)
TargetRange.Copy
.PasteSpecial xlPasteAll
.UnMerge
.ColumnWidth = CombinedColWidth
.Value = TargetRange.Value
.EntireRow.AutoFit
NeededRowHeight = 1.05 * .RowHeight / TargetRange.MergeArea.Rows.Count
.ClearContents
.ClearFormats
.RowHeight = OrigRowHeight
.ColumnWidth = OrigColWidth
End With
'Return NeededRowHeight
NewRowHeight = NeededRowHeight
add a comment |
0
0
0
Moved answer from OP's question:
OrigRowHeight = SafeRange.RowHeight
OrigColWidth = SafeRange.ColumnWidth
CurRow = ActiveCell.Row
CurCol = ActiveCell.Column
NumMergeCols = ActiveCell.MergeArea.Count
LastCol = CurCol + NumMergeCols - 1
For i = CurCol To LastCol
CombinedColWidth = CombinedColWidth + Cells(CurRow, i).ColumnWidth
Next i
' Most of the following code came from Superuser user6261023 (My Thanks)
With SafeSheet.Range(SafeRange.Address)
TargetRange.Copy
.PasteSpecial xlPasteAll
.UnMerge
.ColumnWidth = CombinedColWidth
.Value = TargetRange.Value
.EntireRow.AutoFit
NeededRowHeight = 1.05 * .RowHeight / TargetRange.MergeArea.Rows.Count
.ClearContents
.ClearFormats
.RowHeight = OrigRowHeight
.ColumnWidth = OrigColWidth
End With
'Return NeededRowHeight
NewRowHeight = NeededRowHeight
Moved answer from OP's question:
OrigRowHeight = SafeRange.RowHeight
OrigColWidth = SafeRange.ColumnWidth
CurRow = ActiveCell.Row
CurCol = ActiveCell.Column
NumMergeCols = ActiveCell.MergeArea.Count
LastCol = CurCol + NumMergeCols - 1
For i = CurCol To LastCol
CombinedColWidth = CombinedColWidth + Cells(CurRow, i).ColumnWidth
Next i
' Most of the following code came from Superuser user6261023 (My Thanks)
With SafeSheet.Range(SafeRange.Address)
TargetRange.Copy
.PasteSpecial xlPasteAll
.UnMerge
.ColumnWidth = CombinedColWidth
.Value = TargetRange.Value
.EntireRow.AutoFit
NeededRowHeight = 1.05 * .RowHeight / TargetRange.MergeArea.Rows.Count
.ClearContents
.ClearFormats
.RowHeight = OrigRowHeight
.ColumnWidth = OrigColWidth
End With
'Return NeededRowHeight
NewRowHeight = NeededRowHeight
answered Feb 14 '18 at 23:45
community wiki
K.Dᴀᴠɪs
add a comment |
add a comment |
draft saved
draft discarded
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.
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%2fsuperuser.com%2fquestions%2f1185484%2fsetting-row-height-via-excel-vba-with-merged-cells-and-word-wrap%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
Please note that superuser.com is not a free script/code writing service. If you tell us what you have tried so far (include the scripts/code you are already using) and where you are stuck then we can try to help with specific problems. You should also read How do I ask a good question?.
– DavidPostill♦
Mar 5 '17 at 17:29