where text wraps in a cell, how can the row height be auto set?

[Robert Cape Town]

Plain and simple...................If you have merged cells in the range,
Autofit won't work even with wraptext enabled.

You will need the code if you insist upon using merged cells.

The developers added the merge cells feature in Excel 97 without thinking
about row autofit functionality.

Have not bothered to correct since.


Gord



I have carefully read through this discussion twice and I am no better off than when I started. There are obviously some very clever people trying their best to resolve a very simple problem. But clearly without success, as the same user problem is repeated many times. So to the basics.

Admit it, auto fit row height for a merged and wrapped cell (eg cells B5 to
J5) containing an unknown (and variable) number of charaters does not work,
has never worked.
Also, microsoft has known about this since 1997 or earlier.

As this is a pretty much basic function, microsoft must explain why it has
not been fixed.

It is all very well for the clever people to provide macro answers, but most
users do not know and do not use macros. So there has to be a better way that
the ordinary user can easily implement.

Notwithstanding the above, I am a low level macro user and the code provided
above baffles me. (MS Excel 2007)

I challenge microsoft to respond.

 

[Ruth]

I have merged a number of cells and included text that wraps in the cell. Is
it possible to set the cell so that the height is automatically adjusted to
the height of the text?

 

[Ruth]

I had the same issue. I found a work around for my situation. I made the
column as wider instead of merging cells, then when you select wrap text the
automatic row height works. It may not work for all but does for me.

 

[Gord Dibben]

Ruth

Do you have a question about this previous post you tacked onto?

Short answer...............merged cells do not allow row autofit unless you
employ VBA event code.


Gord Dibben MS Excel MVP

 

[Broughan]

It seems pretty silly that this is not intuitive. One way I know is highlight
all the rows, then on the left hand side of the spreadsheet, where the rows
are numbered double click with the left mouse button on the line between two
of the rows, and bingo

 

[SG]

Thanks Ruth. I tried this and it worked for me. I unmerged the cells and
then double clicked for auto row height and it worked.

 

[Angie]

I need help for a very similar problem:

NO merged cells
NO specialized formatting, and format is consistent across spreadsheet
cells are set to "wrap text"

When I click on "autofit row height", 90% of the rows become the correct
height, while 10% do not. I have a few thousand rows of data, and 26 columns
with varying amounts of text in them. Sometimes the row becomes too short,
sometimes too tall, and it can happen no matter how much or how little text
is in it.

Can I repeat that I DO NOT have any merged cells? I have searched online
for hours and no one seems to have an explanation or a solution.

 

[thepoundster]

Thanks the Code Cage Team! I wanted cells i was typing in to autofit and the
code you supplied worked a treat. I was originally typing into merged cells
for which the code didn't work but after some re-arranging i was able to get
rid of the merged cells. For future reference is there a way of doing this on
merged cells?

 

[Gord Dibben]

You can Autofit rows with merged cells only by using VBA.

One of the many drawbacks to using merged cells.

If you want to go that route...............

See google search thread for code by Greg Wilson. Watch out for word wrap
in the URL which is all one line.

http://groups.google.com/group/micr...60cbeb27874?lnk=st&q=&rnum=4#ca1c160cbeb27874

Note..........this is sheet event code and runs when a cell is changed
manually.

To store the code, right-click on your sheet tab and "View Code"

Copy/paste the code into that sheet module.

Alt + q to return to Excel.

Rows must be formatted to Autofit and cells formatted to Wrap Text


Gord Dibben MS Excel MVP