Determine necessary height in merged cells to accommodate text that exceeds 409.5

[gravanoc]

I have an issue where a cell may have more text than can be displayed within the maximum row height of 409.5, and due to printing concerns the columns must remain a certain width. In order to accommodate this, I can insert one or more blank rows beneath the row with the target cell, then merge each relevant cell above the new row downward, so I'll have, for instance, A2:A3, B2:B3, C2:C3, etc. as merged entities. The problem is that autofit will not work, and manual adjustment is not a viable solution. The font is Times New Roman and size is always 11 & no other font effects applied. With that being the case, I noticed that at 409.5 a cell can handle about 27.25 lines. My question is how do I tell VBA what the best fit row height will be once the cell is merged?

In this example the merge begins at the dashed line.

Blah	Blah	Blah	Blah	Blah	Blah	Blah
Text Is Here	TRUE	TRUE	1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 ----------------- 28 29….	FALSE		YES
More	More	More	More	More	More	More

 

[BSALV]

it's not that simple, this 'll work in only 99% of the cases, adjust that 14.4 if not okay

Sub test()
     'preparation
     r = WorksheetFunction.RandBetween(5, 27)                   'random number between 5 and 27
     s = "column(" & Range("A1").Resize(, r).Address & ")"
     a = Join(Evaluate(s), vbLf)                                '---> a sequence 1, 2, 3, .... until r

     'adjust to number of rows
     Range("A1").Resize(, 2).Value = Array(a, r)                'write that sequence and r to the sheet
     i = Len(Range("A1").Value) - Len(Replace(Range("A1").Value, vbLf, "")) + 1     'count the number of linefeeds + 1 = number of rows in that cell
     Range("A1").EntireRow.RowHeight = 14.4 * i                 'the rowheight is 14.4 * i
End Sub