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?

K.Dᴀᴠɪs
  • 428
Ed Holden
  • 31
  • Please note that https://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 05 '17 at 17:29

1 Answers1

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
K.Dᴀᴠɪs
  • 428