Wrap around setting won't adjust row hieght

[Irvine, Dennis]

I have a particular spreadsheet that refuses to adjust the row height when
"wrapping" text.

I can't figure out why.

I have three ajacent cells "merged" and set to wrap text. But when the
text gets to big, it "wraps" around but the row height stays the same, which
cuts off the text.

anyone have any ideas what might be causing the row height to refuse to
adjust. I have check to see if the "row height" property is manually set
and I have specifically set it to "auto" but it still will not adjust.

 

[RagDyer]

Merged cells will *not automatically* adjust the row height!

 

[JP]

I was only able to duplicate your problem when I unchecked "merge
cells" and checked "wrap text" in the Format Cells box.

(ps- I am using XL 2003)

HTH,
JP

 

[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