Calculating Number of Rows for Merged Cell Text Wrap

[George Lee]

I have lengthy string that I want to display in entirety. I would like to
pick how many cells wide to merge and based on that, have Excel calculate for
me how many rows I need. How is this done?

 

[JLGWhiz]

You can specify the width of the column, set it to wordwrap then use autofit
and it will put the correct number of rows.

 

[JLGWhiz]

Here is some more info from Jim Rech about merged cells and autofit.

Unfortunately autofit ignores merged cells. The only workaround I know of
is a macro that simulates autofit for row heights. Of course it's not
automatic unless you call it from the worksheet change event.

''Simulates row height autofit for a merged cell if the active cell..
'' is merged.
'' has Wrap Text set.
'' includes only 1 row.
''Unlike real autosizing the macro only increases row height
'' (if needed). It does not reduce row height because another
'' merged cell on the same row may needed a greater height
'' than the active cell.
Sub AutoFitMergedCellRowHeight()
Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
Dim CurrCell As Range
Dim ActiveCellWidth As Single, PossNewRowHeight As Single
If ActiveCell.MergeCells Then
With ActiveCell.MergeArea
If .Rows.Count = 1 And .WrapText = True Then
Application.ScreenUpdating = False
CurrentRowHeight = .RowHeight
ActiveCellWidth = ActiveCell.ColumnWidth
For Each CurrCell In Selection
MergedCellRgWidth = CurrCell.ColumnWidth +
MergedCellRgWidth
Next
.MergeCells = False
.Cells(1).ColumnWidth = MergedCellRgWidth
.EntireRow.AutoFit
PossNewRowHeight = .RowHeight
.Cells(1).ColumnWidth = ActiveCellWidth
.MergeCells = True
.RowHeight = IIf(CurrentRowHeight > PossNewRowHeight, _
CurrentRowHeight, PossNewRowHeight)
End If
End With
End If
End Sub

 

[George Lee]

I found that page and have used it. Quite good. This was one of those
functions that seems it should have been atomic to Excel. Thanks.

 

[Josh]

This works well for autofitting with content. But if I clear the content of
the cell, the row does not shrink to fit the empty cell (in other words,
revert to the default row height).

Is there code you can provide to do this?

Thanks in advance.