Wrapping Text in a Merged Cell

[jeh1121]

I can not get the Text Wrapping feature to work in a merged cell. Can this be
done?

 

[Dave Peterson]

Text wrapping in a merged cell should work ok.

But you may need to adjust the rowheight manually to see everything you want.

And if the text just seems to fade away after a few lines of really long text,
you can add alt-enters (to force a newline within the cell) every 80-100
characters to see more.

 

[Gord Dibben]

Text-wrap works fine in a merged cell.

What won't work is Autofit to expand the wrapped text.

The only way around it is to...............

1. DON'T USE merged cells. They cause no end of problems with copying,
pasting, sorting, filtering and, as you have found, with autofit.

2. Use event code from Greg Wilson

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewRwHt As Single
Dim cWdth As Single, MrgeWdth As Single
Dim c As Range, cc As Range
Dim ma As Range

With Target
If .MergeCells And .WrapText Then
Set c = Target.Cells(1, 1)
cWdth = c.ColumnWidth
Set ma = c.MergeArea
For Each cc In ma.Cells
MrgeWdth = MrgeWdth + cc.ColumnWidth
Next
Application.ScreenUpdating = False
ma.MergeCells = False
c.ColumnWidth = MrgeWdth
c.EntireRow.AutoFit
NewRwHt = c.RowHeight
c.ColumnWidth = cWdth
ma.MergeCells = True
ma.RowHeight = NewRwHt
cWdth = 0: MrgeWdth = 0
Application.ScreenUpdating = True
End If
End With
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code".

Copy/paste the code into that sheet module.


Gord Dibben MS Excel MVP