4th Floor, 4 Tabernacle Street London EC2A 4LU

Resize Merged Excel Cells – VBA


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
Application.ScreenUpdating = False
     ma.MergeCells = False
      c.ColumnWidth = MrgeWdth
        NewRwHt = c.RowHeight
       c.ColumnWidth = cWdth
     ma.MergeCells = True
    ma.RowHeight = NewRwHt
   cWdth = 0: MrgeWdth = 0
Application.ScreenUpdating = True
End If
End With