The Row Height Autofit command does not work.

[Guest]

I cannot seem to get the row height Autofit function to work when typing text
in Excel. When in the cell or cells I want to expand automatically and go to
format >row>auto fit the cell shrinks to one line though I have typed
multiple lines of text. How do I get it to expand to show all the lines
automatically?

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...dg=microsoft.public.excel.worksheet.functions

 

[Guest]

1. Have you set WrapText to True? (Format > Cells > Alignment tab > Wrap text)
2. Is the cell merged?

Greg

 

[Guest]

Text wrap is True. The cells are merged...I read in previous submittals that
might be my problem. Is there a solution?

 

[Guest]

Merged cells do not support autofit. Generally, you should try to avoid them.
A couple of solutions to my knowledge have been developed:

1. Have a cell in the same row as the merged range that contains a formula
that references the active cell of the merged range (e.g. "=A5"). The column
width of this cell should be exactly the same as the width of the merged
range and the font size should also be the same. Wrap text should be set to
True. Use the Worksheet_Change event to monitor change to the active cell of
the merged range and force autofit of the cell containing the formula. The
merged range will autofit at the same time because it is in the same row.

2. The following is an adaption of code originally developed by Jim Rech
and is from a recent post of mine. Paste to the worksheet's code module:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewRwHt As Single
Dim cWdth As Single, MrgeWdth As Single
Dim c As Range, cc As Range
Dim ma As Range

With Target
If .MergeCells And .WrapText Then
Set c = Target.Cells(1, 1)
cWdth = c.ColumnWidth
Set ma = c.MergeArea
For Each cc In ma.Cells
MrgeWdth = MrgeWdth + cc.ColumnWidth
Next
Application.ScreenUpdating = False
ma.MergeCells = False
c.ColumnWidth = MrgeWdth
c.EntireRow.AutoFit
NewRwHt = c.RowHeight
c.ColumnWidth = cWdth
ma.MergeCells = True
ma.RowHeight = NewRwHt
cWdth = 0: MrgeWdth = 0
Application.ScreenUpdating = True
End If
End With
End Sub

Regards,
Greg

 

[Gord Dibben]

Autofit does not work with merged cells.

There is a solution........DO NOT USE merged cells.

You can resize manually or via Macro. Jim Rech has written
code for this.

http://groups.google.com/groups?threadm=e1$1uzL1BHA.1784@tkmsftngp05

Also revised code for special cases.

http://snipurl.com/dfsm


Gord Dibben MS Excel MVP