AutoFitMergedCellRowHeight - Jim Rech

[David]

Greetings,
Jim Rech's code works great for this, see:
http://groups.google.com/groups?threadm=e1$1uzL1BHA.1784@tkmsftngp05

But is it possible to trigger this with an event so that when the selection
is changed FROM the merged cell the code runs for the merged cell (ie would
be equivalent to BeforeSelectionChange)
Many Thanks

 

[Greg Wilson]

Paste to the worksheet's code module:

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

The above is an adaption of Jim Rech's code from a recent post of mine.

Regards,
Greg