Macro to show wraped text in merged cells

[Milky Bar Kid]

Has anybody written a macro that will set the correct row
height to display all of the text in horizontally merged
cells in a single row? The problem arises if you merge
(say) 4 cells and then enter text which is greater than
the width of those cells and select wrap text.
The "excess" text disappears from view, but reappears
properly wrapped if you double the row height. So the
macro needs to know the size of the entered text and the
width of the merged cells and could then presumably
adjust the row height accordingly.
Or is there another way? Unfortunately text boxes are of
no use in the application I need this for.
Many thanks in advance to any helpers.
MBK

 

[Jim Rech]

This works on the active cell.

''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
| Has anybody written a macro that will set the correct row
| height to display all of the text in horizontally merged
| cells in a single row? The problem arises if you merge
| (say) 4 cells and then enter text which is greater than
| the width of those cells and select wrap text.
| The "excess" text disappears from view, but reappears
| properly wrapped if you double the row height. So the
| macro needs to know the size of the entered text and the
| width of the merged cells and could then presumably
| adjust the row height accordingly.
| Or is there another way? Unfortunately text boxes are of
| no use in the application I need this for.
| Many thanks in advance to any helpers.
| MBK
|