Row Height AutoFit doesn't work

Question

So, today is my bad day
I discovered that excel row AutoFit - doesn't work

a = "Please"
b = "Do AutoFit"
range01.Value = a & vbNewLine & b  
range01.EntireRow.AutoFit // First and last line are horizontally cutted  

Chr(10) instead of vbNewLine - doesn't help
It works only if I write without line breaks:

range01.value = a & b
range01.EntireRow.AutoFit  //  works !!!  Thanks to Microsoft !

Answer 1

Make sure that the cells that have line breaks in them have "Wrap Text" turned on. You can turn it on with VBA like so:

Sub test1()
    Dim a As String, b As String
    a = "Please"
    b = "Do AutoFit"
    Range("A1").Value = a & vbNewLine & b
    Range("A1").WrapText = True
    Range("A1").EntireRow.AutoFit
End Sub

Answer 2

Autofit doesn't work on merged cells. My guess is that's what you are trying to do.

Work around this by simulating the autofit on a single cell with the same width as your merged cell and set the height it gives you.

Something like this:

Dim YourString As String, a As String, b As String
Dim TempLoc as Range

a = "Please"
b = "Do AutoFit"
YourString = a & vbNewLine & b
Set TempLoc = range01.offset(0, 10) ' Given the column 10 columns to the right has the exact same witdh as your merged cell.

' Autofit a single cell to get correct height
With TempLoc
       .Value = YourString
       .WrapText = True
       .EntireRow.AutoFit
       .RowHeight = TempLoc.RowHeight
       .ClearContents
End With