Row Height AutoFit of Merged regions

May 12, 2010 at 10:29 AM

Hi,

Has anyone found a good way to deal with setting row heights (in place of Autofit) when there are merged cells in the focus row as Excel does not allow AutoFit to set row heights with merged cells. Brief testing tells me it is the same when creating xls files with NPOI.

Using Excel automation I have previously used the following approach..

      If objCurrentCell.MergeCells Then
                With objCurrentCell.MergeArea
                    If .Rows.Count = 1 And .WrapText = True Then
                        objExcelApp.ScreenUpdating = False
                        CurrentRowHeight = .RowHeight
                        ActiveCellWidth = objCurrentCell.ColumnWidth

                        For Each CurrCell In .Cells
                            MergedCellRgWidth = CurrCell.ColumnWidth + MergedCellRgWidth
                        Next
                        .MergeCells = False
                        .Cells(1).ColumnWidth = MergedCellRgWidth
                        .EntireRow.AutoFit()
                        PossNewRowHeight = .RowHeight
                        .Cells(1).ColumnWidth = ActiveCellWidth
                        .MergeCells = True
                        .RowHeight = PossNewRowHeight
                    End If
                End With
            Else
                objCurrentCell.EntireRow.AutoFit()

            End If