row heights

[Guest]

I have spreadsheets which have merged cells to insert comments on variances.
The format of the merged cells is set to wrap text if the text string is
longer than the cell. How can I format the row/cell so that if the text does
wrap to more than one line, then the row automatically resizes so that the
entire text string is visible.

Thanks for your help

 

[Gord Dibben]

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

One more victim of "merged cells".

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

You need VBA event code or a macro to do that.

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

Copy/paste the above into that sheet module.


Gord Dibben MS Excel MVP