Gord - can you please explain to me in laymens terms what you are supposed to
copy and paste? That whole blurb from "Private Sub..." to "... End Sub"? And
am I to paste it in the actual merged cell I wish to Autofit?

THANKS!!!!

"Gord Dibben" wrote:

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

Copy/paste the code into that sheet module.


Gord Dibben MS Excel MVP

On Tue, 31 Jul 2007 13:04:05 -0700, Taurus_ma
wrote:

How do I autofit text inside of a merged cell (3 cells that have been merged
into one)? I would like the row to expand as needed, depending on the amount
of text I input. But I'm learning that autofit only works inside of a
single, un-merged cell.

Is there anyway around this?

Thanks!