Hi,

I have several cells merged and set to wrap the text. I need the merged
cells to grow with the amount of data.

It does this if the cells are NOT merged, but not with merged cells.

Does anyone have a solution please?
Many thanks

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 to do that.

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


Gord Dibben MS Excel MVP

On Mon, 25 Jun 2007 06:01:03 -0700, olwenh
wrote:

Hi,

I have several cells merged and set to wrap the text. I need the merged
cells to grow with the amount of data.

It does this if the cells are NOT merged, but not with merged cells.

Does anyone have a solution please?
Many thanks

Thanks very much - I'll give it a go. Really grateful


"Gord Dibben" wrote:

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 to do that.

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


Gord Dibben MS Excel MVP

On Mon, 25 Jun 2007 06:01:03 -0700, olwenh
wrote:

Hi,

I have several cells merged and set to wrap the text. I need the merged
cells to grow with the amount of data.

It does this if the cells are NOT merged, but not with merged cells.

Does anyone have a solution please?
Many thanks

Works a treat - fantastic - thanks

"olwenh" wrote:

Hi,

I have several cells merged and set to wrap the text. I need the merged
cells to grow with the amount of data.

It does this if the cells are NOT merged, but not with merged cells.

Does anyone have a solution please?
Many thanks