[Excel] AutoFit the row height?

[EWCguy]

I see that I can use range.AutoFit to set the column width to be the best fit for the contents. I need this capability for row height instead.

I'm accepting user comments as part of a macro. The macro selects some cells, merges them, then puts in the comment. From MSO developer ref in Excel, it looks like setting the cell property for "wrapText" to true that it is supposed to adjust the row height automatically. I have not found this to be the case.

"Microsoft Excel will change the row height of the range, if necessary, to accommodate the text in the range."

I found a formula here in the forums that someone was using to adjust the row height using With range:

.RowHeight = .Font.Size * (Len(xlWksht.Range("A" & ii + 1).text) - Len("Comments:")) / w + rht + (rht - .Font.Size)

Does anyone know of a simpler method for setting the row height to be just enough to fully display the wrapped cell contents?

Thanks for the great help!
~ Aaron

[vbfbryce]

Rows(1).AutoFit

will auto fit row 1

[EWCguy]

Bryce, thanks for the suggestion. Unfortunately, it didn't work. The code ran fine but the result was not as hoped.

It should be noted from the resulting file from my process, that I cannot even dbl-click the row divider and get Excel to autofit the row. Here's an image of what I have. The rows indicated by the red dots should be adjusted by the code, but it's not happening.

    Dim curRow As Integer
    curRow = 58 + comCount
'Select and merge cells for comment, write comment & resize row height
    With Worksheets("Summary").Range(Cells(curRow, commentCol), Cells(curRow, mergeCol))
        .MergeCells = True
        .Value = txtComment.Value
        .WrapText = True
        .VerticalAlignment = True
        .Rows(curRow).AutoFit
    End With

Any other thoughts?

[vbfbryce]

Any chance there is a "cell merge" in that area? That might be impacting?

[EWCguy]

Cells merged, absolutely. That is the desired situation. Thanks.

[vbfbryce]

My bad...did not read through your comments about merging and wrapping...

I think having merged cells can causes issues with auto fitting, but can't test right at the moment.

EDIT: Ignore this, was thinking of something else!