Row adjust not working for merged cells

Brass Contributor

 

I am using MS 365 v2102

 

There is a merged cell that will contain a long text. Typically used for notes.

When I touch "wrap text", the merged cell with long text only shows one line.

I manually make the row several lines tall. If I re-wrap it goes back to 1.

 

Wrap text is not working with merged cells. It assigns only a line tall.

 

Please, identify basic functionality issues first. 

 

 

I tried a similar post for 2016 but it is a different thing.

https://techcommunity.microsoft.com/t5/excel/autofit-row-height-with-wrap-text-not-working-on-2016-e...

10 Replies

@EduardoSR 

This is one of the many reasons you should avoid merging cells.

 

Are your cells merged horizontally (i.e. in a row), or merged vertically (i.e. in a column), or both?

Horizontally, just one row. Maybe the problem is with all the merges. Haven't checked

Merged cell have always been problematic, but anyway you still need them for formatting

@EduardoSR 

Does your text contain the line feeds (Alt/Enter or CHAR(10)) or are you relying on the cell width to generate the now line?

@Hans Vogelaar 

I find that, with dynamic arrays, merged cells trouble me less.  My source data would be held in Tables and the calculated values are all dynamic arrays.  I have very little need to make manual selections in regions outside these named objects, so I do not come into conflict with the merged cells to any great extent.  One use I have for merged cells, is to display the result of FORMULATEXT which, these days, may well run over 10 or more rows when using combinations of LAMBDA and LET.

I have tried both: plain text and plain text with Alt+Enters. No formulas nor dynamic arrays
None work in merged cells
Both work in single simple cells

@EduardoSR 

It should simply be a matter of whether the .wrap property of the range is set or not.  The VBA help shows:

This property returns True if text is wrapped in all cells within the specified range, False if text is not wrapped in all cells within the specified range.  Microsoft Excel will change the row height of the range, if necessary, to accommodate the text in the range.

 

The same applies whether you are accessing the setting via code or buttons on the user interface.  It seems that merged cells do not adjust the row heights to accommodate the text, though.  That has to be done manually.

 

@Peter Bartholomew 

Merged cells cause a lot of problems in Excel VBA. One can work around them, but it's a pain in the behind... :(

@EduardoSR 

If I use Center Across Selection instead of merging cells, Excel automatically fits the row height for me.

 

There is VBA code to autofit the row height of merged cells, but (a) it is complicated, (b) it works less than perfectly, and (c) it disables Undo.

I would also use 'Center across Selection' for centred headings, though it makes little difference to ones ability to select an Excel Table column since that does not accept entire column selections.
I wasn't proposing a VBA solution, I had simply used the object model as a source of information on the 'wrap' property. I hope I did not confuse the OP.
I understood you wanted to do some "reverse engineering via VBA" to find out a solution.

In my case the other centred options do no seem to work for me. In fact it is the same solution that was stated at the link.

@EduardoSR 

Please use Excel add-in "AutoFitRowEx".

This add-in will solve the issue.

 

[Github - Releases · toowaki/AutoFitRowEx]

https://github.com/toowaki/AutoFitRowEx/releases