Row Height-Auto Adjust to Content?



Hi everyone,

Is it possible to set Excel Rows to automatically adjust to the content in
any rows?
I'm working on a significantly large excel sheet and prefer to print only
one sheet of paper across; however I'm trying to squeeze a couple more
columns in there that might overflow to the next sheet across. I'm thinking
of shrinking the columns' width of some columns to compromise the space for
couple columns that I'm adding. But as you know, some Addresses are short
and some are long. I don't want to increase the height of all rows when I
decrease the columns width, and I also don't want to set the formatting to
"Shrink to Fit" as this will cause some text to be shrink down to the size
that is unreadable.

Is there a way to set the rows height to auto-adjust to the content? Such
that the height will increase when the text is overflowing to 2 rows or 3
rows and so on? I know "Wrap Text" will wrap the text in the column width
defined by user but the height won't increase.

Help please!




Gord Dibben

In conjunction with wrap text Format>Row>Autofit in XL2003

Home>Format>Autfit row height in XL2007

Note: if any merged cells in the row Autofit will not work.

Merged cells have to be dealt with through VBA.

Gord Dibben MS Excel MVP


Hi Gord,

Thank for your answer. It's helpful to know that merged cells won't work
with this method.
Just out of curiosity, why is it impossible to format the row to autofit
with merged cells?

Also, is it possible to set MINIMUM row height when setting AutoFit so that
it won't autofit to the text size? I'd like the rows to have autofit only
when the texts won't fit in one row.

Thank you,

Gord Dibben

Why impossible.............not known why that wasn't taken into account when
some developer of Excel 97 thought merged cells would be a great idea but
forgot about all the problems they would cause.

I don't understand point two.

That's what autofit does. The row won't expand if the text fits on one row.

Note: autofit is an entire row function, not a cell function. Autofit
expands to fit the cell with the most text when wraptext is enabled.



Point two explanation:

If I'd like to set my rows height to be .22 inches in height and the font is
only 10 points, when I set the rows to be Auto-Fit, it will automatically
shrink back down to .18 inches to "fit" the 10 points font.
Is it possible to have the rows height default to .22" even with the
Auto-Fit is on and only expand the rows height when it needs and extra space
for Second line of text?

Thank you!!



Gord Dibben

Excel does not set rowheights in inches.............points or pixels only.

Do you mean 22 pixels which is about 16.5 points?

Default ppi for 10 point Arial is 12.75 for example.

This sheet event code will autofit the target row to expand to fit text but
leave a minimum of 16.5 points.

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
.WrapText = True
If .RowHeight < 16.5 Then
.RowHeight = 16.5
End If
End With
End Sub



Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question