Forum Discussion
Row adjust not working for merged cells
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?
Merged cell have always been problematic, but anyway you still need them for formatting
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.
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?
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.
Merged cells cause a lot of problems in Excel VBA. One can work around them, but it's a pain in the behind... 😞
- 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 cellsIt 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.
