MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Row Height Auto-Fit not working

Posted by Amy Bowser on September 16, 2000 3:18 PM

I'm using Excel 97. For some reason, when I print the spreadsheet, the column with the longest amount of text gets cut off. It doesn't print the last line or two of text. I have turned on row auto-fit, but it doesn't make a difference.

Through testing, I figured out that if I add two alt-enter's to the end of the cell, it prints correctly. Since I never know which column will have the longest cell, I thought maybe I could add two new lines at the end of every cell in the 8 columns.

I tried to write a macro to add the new lines to each of the cells and then loop through the entire sheet until the EOF, but it actually records the value in each cell and replaces each cell with the wrong text plus the two new lines. How do I add just two new lines?

Or why is Excel doing this in the first place? I've checked all the format settings I can think of. What is the point of auto-fit if it doesn't work? This has happened on several spreadsheets in the last year. We're using Times Roman for the font, does that have something to do with it?

Thanks for your help!

Posted by David on September 19, 0100 1:55 AM

bit mesy but try running this before printing
Sub Macro1()

firstEmptyRow = ActiveSheet.UsedRange.Cells(ActiveSheet.UsedRange.Cells.Count).Row
FinalRow = Range("A65536").End(xlUp).Row
Do While Cells(1, FinalRow) = ""
FinalRow = FinalRow - 1
finalcolumn = Cells(firstEmptyRow, FinalRow).Address
finalcolumn = "$A$1:" & finalcolumn
ActiveSheet.PageSetup.PrintArea = finalcolumn

End Sub

see if it works for you

Posted by Michael Liu on September 19, 0100 10:58 AM

okay, now I know of the problem you speak of.

You could select the whole sheet, expand one row to be very very large,
and then auto-fit the rows. It seems to work if you "overdo" the row

Posted by Michael Liu on September 18, 0100 1:28 PM

Auto-fit is not a dynamic feature. If you set the
height of a row, and then later add text that will exceed that
height, the row will not auto-expand. You would need to re-autofit
the row, which I usually do by double-clicking with the pointer
in between the two row headers.

You may want to make sure your cells are using word wrap.
Or, using Alt-Enter inserts a line break in the cell and
forces the following contents to expand the cell.

Posted by Amy Bowser on September 18, 0100 9:11 PM

I have been redoing the "auto-fit" after we make edits. I usually highlight the entire sheet and autofit all the rows at once. The problem is, after I autofit, it still doesn't expand enough for the text. That's the part I don't understand.

The word wrap is definitely on for all the columns.

Thanks for responding.