autofit text inside merged cells

[Guest]

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!

 

[Gord Dibben]

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

 

[Guest]

I followed this instruction and it worked well until I added protection to
the sheet. The cell I needed to expand was not locked but the box no longer
grew. Is there additional part(s) to the code that would allow this to work?

 

[Gord Dibben]

Karen

When you protect the sheet you will have to checkmark "format cells", "format
rows" and "format columns.

I would uncheck "select locked cells" at the same time.

If you don't want that, you could add an unprotect line to the code then
re-protect after it has run.

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
ActiveSheet.Unprotect Password:="justme"
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
ActiveSheet.Protect Password:="justme"
Application.ScreenUpdating = True
End If
End With
End Sub


Gord


On Tue, 31 Jul 2007 17:34:01 -0700, Karen Whelan <Karen

 

[Guest]

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!!!!