Tip Printed from ExcelTip.com
Adjust row height of merged cells using VBA in Microsoft Excel


Question:
Merged cells do not have the correct height after a row break. How can I correct this?


Answer:
Insert the following code in the standard module.










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 .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

Application.ScreenUpdating = True

End Sub