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.

"JLGWhiz" wrote:

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


--
Jim Rech
Excel MVP




"George Lee" wrote:

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?