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