Automatically Setting Row Height with Merged Cells

[katherineolson]

Hi All,
I am working on a C# application that requires me to enter multi-line
data into an Excel 2003 spreadsheet. I am wondering how to
automatically resize the row height of merged cells to accomodate all
of this text. I attempted to use a dummy cell with the same column
width as the merged cells to discern the row height, but this is not
working.

Below is a snippet of the code that I am using...

// dummy cell location
string cell = "AA19";
string tempRowHeight = ""; // object for storing original
dummy cell height
// Create a non-merged dummy cell so we can determine the
necessary height of the row
Excel.Range dummyCell =
(Excel.Range)worksheet.get_Range(cell, cell);
// Get the original row height so that we can reset the
dummy cell when we are finished
tempRowHeight = dummyCell.RowHeight.ToString();
// Set the dummyCell column width to the width of the
merged range
dummyCell.ColumnWidth = columnWidth;

dummyCell.WrapText = true; // force the cell to resize

dummyCell.Value2 = locText;

// get the rowHeight of the resized cell
string rowHeight = dummyCell.RowHeight.ToString();

// remove the text from the dummy cell before continuing
dummyCell.Value2 = "";
dummyCell.RowHeight = tempRowHeight;
// release the com objects

System.Runtime.InteropServices.Marshal.FinalReleaseComObject(dummyCell);
dummyCell = null;

return rowHeight

I then use the calculated rowHeight to set the rowHeight of the merged
area. Has anyone else attempted a solution like this with any success?


Any help would be much appreciated!

Katherine

 

[Tom Ogilvy]

Yes, it has been attempted with success.

 

[katherineolson]

Thanks for you reply Tom -- I am still having no luck getting it to
work. Is it possible that I may have missed a vital step in my code?

 

[Tom Ogilvy]

Code by Jim Rech

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

End Sub

The above is VBA and does it in place. Perhaps it will give you some
insights.