Expanding merged cells

[Michalek]

In Excel 2003, I have several cells merged across columns into one cell. I
format the cell to wrap text and want the cell (row) height to expand as
text is typed in. When doing this in a single, unmerged, cell, it works
fine. The text wraps and the row height expands. When trying in the merged
cell, it does not wrap the text or expand the cell height. I can manually
change the row height to show all text, but desire it to be automatic.
Using format>row>autoheight does not do anything either. Any suggestions?
Thanks.

 

[Gord Dibben]

Long audible sigh here.................

One more victim of "merged cells" and its disadvantages that far outweigh the
advantages.

It may be better to use the "Center Across Selection" from
Cells>Format>Alignment.

Wrap Text works fine on merged cells, but Autofit does not work.

You need VBA event code to do that.

The cells must be set for wrap text and Autofit before this code will work.

Here is 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 event code. Right-click on the sheet tab and "View Code".

Copy/paste the code into that sheet module.


Gord Dibben MS Excel MVP

 

[Michalek]

Thanks, GD. Actually, I found your similar replies on the microsoft excel
discussion group after posting this and didn't get a chance to reply here to
"cancel" the request. The macro works fine. Just curious why the
formatting doesn't work in excel.

 

[Gord Dibben]

Just one of the quirks with merged cells.

Why.....?? Only the developers know.......maybe<g>


Gord