Automatic cell height adjustment

[TPFKAS]

I have a VBA project that creates tables in an Excel with content that is then copied to a Word document. I encounter a problem with automatic cell heights. When cells are filled with text sometimes the automatic height adjustement does not work properly. It looks like if there is text that almost completly filles the cell the heigh of the cell adjusts as if there is yet another empty line below my text. I have loked around on the internet and I found that there is a very peculiar solution. Zoom the spreadhsheet in to 150% and then click on the rowdivider it works correctly. I can then zoom back to 100% and it still looks OK. However, I can not get this workaround to work in VBA. Anybody any idea how to solve this problem? Thanks in advance.

 

[bobsan42]

There are some discussions on this subject, but I have found no confirmation of my opinion:
The reason for the extra space (and sometimes the opposite) is the way things will look when printed. It is not exactly WYSIWYG. At some zoom level you will get a better view of how things will look when printed, but at others it will be completely different.
I couldn't confirm that changing the zoom level fixes the issue.
Rather, the zoom level changes the text layout in the cell, and the way you see things will vary depending on the font type and size.
I made a few tests and found Calibri font to give best match @ 100% zoom level (with very small variation while zooming) .
I don't know if I manage to explain it well, but I don't think it is something that can be resolved by code.

 

[TPFKAS]

Hi Bobsan,
Thanks for your reply. Your explanation is clear to me. I also did a small test with Calibri, but that shows the same behaviour. I tried other fonts as well, but they all act the same way. For dome reason, Excel seems to think that if your text comes close to the right border of a cell it needs to be expanded with an extra line.
I have doubts if this is a WYSWYG issue, because when the table is copied to Word It will copy the height exactly as it is in Excel, but in Word I can then shrink the cells and they will snap to th the right height. Very peculiar. Must be some kind of bug in Excel.

 

[TPFKAS]

I was thinking of a possible solution: is there an option to first copy the table to Word and then apply an autofit on all of the rows on the copied table inside Word?

 

[TPFKAS]

I found a simple workaround: first disable Text Wrap in the cells in Excel and then copy the table to Word.
In Word the height of cells in the table are automatically adjusted to the content correctly.