Row autoheight for merged cells

[René van Kessel]

Hello,

In Excel I have merged cells to one cell. Now I can't let Excel
automatically set the height of the row. The merged cell contains multiple
lines (#10).
When a row does not contain any merged cells then this works fine.

From Delphi I tried following without succes:

Excel.Rows[2].EntireRow.AutoFit;

Any ideas?
Thanks.
René

 

[Bernie Deitrick]

Rene,

You need to use a macro - see below, written by Jim Rech. Select the cell
and run the sub.

HTH,
Bernie
MS Excel MVP

Sub AutoFitMergedCellRowHeight()
Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
Dim CurrCell As Range
Dim ActiveCellWidth As Single, PossNewRowHeight As Single
If ActiveCell.MergeCells Then
With ActiveCell.MergeArea
If .Rows.Count = 1 And .Cells(1).WrapText = True Then
Application.ScreenUpdating = False
CurrentRowHeight = .RowHeight
ActiveCellWidth = ActiveCell.ColumnWidth
For Each CurrCell In Selection
MergedCellRgWidth = CurrCell.ColumnWidth + _
MergedCellRgWidth
Next
.MergeCells = False
.Cells(1).ColumnWidth = MergedCellRgWidth
.EntireRow.AutoFit
PossNewRowHeight = .RowHeight
.Cells(1).ColumnWidth = ActiveCellWidth
.MergeCells = True
.RowHeight = IIf(CurrentRowHeight > PossNewRowHeight, _
CurrentRowHeight, PossNewRowHeight)
End If
End With
End If

End Sub