programmatically autofitting row height

[RobBroekhuis]

Hi all,
I just found out that the row-autofit feature in Excel doesn't work on a merged, word-wrapped cell, for some reason. I'm trying to figure out a way to set the row height using a sheetchange event, which would require me to calculate the height of the changed cell's contents. Is there a relatively straightforward way to do this? Or, is there a different way to make Excel do this properly?
Rob

 

[Mossoft]

I think the last thing on the minds of Excel developers was merged cells and the facility was shoved into the product at the last minute.

Cant find an easy way but 2 avenues to explore:

1) create a text/picture box with wordwrap set to true and the width set to the width of the column in question. Put the text in the box, get the height and make the worksheet row height the same.

or

2) attempt to search the contents of the cell for a soft line break. Multiply the row height by the number found in the text + 1.

Good luck.

M

 

[RobBroekhuis]

Mossoft - thanks for your suggestions. Your 2nd suggestions was along the lines of what I was thinking of, but I'm not aware of a method for searching a soft line break.
Your 1st suggestion also makes sense, but I'm developing this for people who are used to the standard Excel worksheets, entering text and values into cells, not text boxes.

I actually did find a work-around: I set an unused column to a width equal to the combined width of the merged cell, and turned word wrap on for the cell in that column and the row of the merged cell, and put a formula in that cell referring directly to the merged cell. Then I hid the previously unused column. Now Excel's autofit method works (it fits the row height according to the height of the hidden cell), although I still have to trigger it using the worksheet's change event. A little messy, but hidden from view and does the trick.
Rob