Question is on a problem I found posted in internet many times, with NO
proper answer. Postings dates range at least from 2001 up to 2010. Text is
not short, but I have seen many long threads just to get all (or part of)
this information together.
I work with Excel 2007 (occurs with previous versions as well) and Windows
XP (both fully updated on Feb.08, 2010), scanned for malware (besides
continuous use of Microsoft Firewall and Microsoft Security Essentials as
anti-malware protection, continuously updated). The spreadsheet has two
columns, 100 rows, all in text format, words of 8 to 12 characters separated
by blank spaces, all text wrapped, no merged cells (I repeat: NO merged
cells), each cell with maximum 60 characters. Column €œA€� has all cells with
font Arial, 11, bold. Column €œB€� has all cells with font Arial, 10, normal
(not bold). All cells adjusted to left, up. Cells and worksheet are not
protected. Tried and confirmed the same problem both saving in 97-2003
compatibility mode (.xls) and also saving in full Excel 2007 (.xlsx). Row
height can be manually adjusted with no problem. But to make sure I am free
of previous height manual adjustment (either dragging boundary, using VBA, or
by [cell format row height number]), I just created a new sheet with
default settings, copied €“ value only €“ data from another sheet, then
formatted both columns as described above.
Problem: I select whole spreadsheet and then €œcell format auto row
height€�, and it works properly for some cells and not for other. For some
cells it adjusts hiding last line completely, for other cells it adjusts
hiding last line partially.
This is the problem. Question: What shall we do to make [autofit row height]
work properly always?
Additionally, I found that when I change the column width, there is a range
of width such that the text of a cell fits complete in two lines, for
example, and autofit row height works well. When I gradually reduce column
width just to make the text wrap to three lines, then autofit row height does
not work properly and the third line keeps hidden. Further gradually reducing
column width, at a certain point autofit row height comes back to proper
functioning and adjusts row height to show all three lines. I found no way to
control this issue such it would be acceptable for daily use. It happens
differently for cells with different words length in the same column.
Naturally, it is not acceptable either to adjust manually each row, or else
to adjust all rows for the height of the cell with the longest text. Equally
not acceptable would be to depend on a macro to get good control.
Even If (IF) this is the only situation of wrong adjustment (I do not know
if this is the only case, but this is actually one case), and there is no
proper solution, then it is a MICROSOFT EXCEL BUG €“ sure, and quite aged!
Sorry, MS cannot say it is just a design option (as it says concerning the
€œmerged cells height adjustment€� issue). It has a short (and ugly) name: b u
g.