How to use Row.AutoFit for merged Excel cells

The Row.AutoFit method of Excel (2003) ignores merged cells. The MergeAndFit function below can be used to auto-fit the row height for a merged cell range. (It is an enhanced version of the AutoFitMergedCellRowHeight function of Jim Rech.)

' Merges a cell range, wraps text and auto-fits the row height.
Public Sub MergeAndFit(ByVal r As Range)
   ' Because the Row.AutoFit method ignores merged cells, we temporarily expand the first column of
   ' the cell range to the width of the whole cell range and call AutoFit with the un-merged cell.
   Dim Row As Range: Set Row = r.Rows(1)
   Dim Column1 As Range: Set Column1 = r.Columns(1)
   Dim RangeWidth: RangeWidth = r.Width
   Dim OldColumn1Width: OldColumn1Width = Column1.ColumnWidth
   Dim i As Integer
   For i = 1 To 3                     ' approximation of Column1.ColumnWidth in 3 steps
      Column1.ColumnWidth = RangeWidth / Column1.Width * Column1.ColumnWidth
      Next
   r.WrapText = True
   r.MergeCells = False
   Dim OldRowHeight: OldRowHeight = Row.RowHeight
   Row.AutoFit
   Dim FitRowHeight: FitRowHeight = Row.RowHeight
   r.MergeCells = True
   Column1.ColumnWidth = OldColumn1Width
   Row.RowHeight = IIf(FitRowHeight > OldRowHeight, FitRowHeight, OldRowHeight)
   End Sub

Example of how to use the MergeAndFit function

Range("B2") = "abc " & String(100,"x") & " xyz"
MergeAndFit Range("B2:D2")

Author: Christian d'Heureuse (www.source-code.biz, www.inventec.ch/chdh)
License: Free / LGPL
Index