AutoFit - Merged Cells

[David A.]

Hey,
I have a row with a series of merged cells in it, (b43:m43, makes up the
merged cell). I need to autofit text in that merged cell. Every time I use
AutoFit it shrinks the entire row to one line (12.50). How do I get it to
autofit based on what is in the merged cells b43:m43? This has been driving
me crasy for a week now.

Thanx

 

[Gord Dibben]

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

One more victim of "merged cells".

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.

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

 

[Bill Renaud]

I believe that merged cells to not participate in AutoFit operations. You
might have to write a macro to copy the contents to a new, temporary
worksheet, AutoFit the cell there, measure the width of that cell, then set
the width of the cell on your original worksheet to this value.

Does this range have Wrap Text turned on? Do you want the row height to be
a multiple of the normal row height? Then you have an even more complex
problem at hand.

 

[Vita]

There was a debug problem while I protect sheet with this event code. Please help me to solve this problem.

Thank you.