Excel 2007 Autofit

[SJHMCSE]

I am using Excel 2007. I’m working on a spreadsheet where I would like to
have a row (which has been merged and wrapped) height adjust automatically as
the writing wraps around. I’ve gone to the Home Tab, to the Cell Block, to
Format, to Autofit Row Height, and it doesn’t work. I’ve tried clicking
twice on the line item line to get it to automatically enlarge row, but that
didn’t work either. Any suggestions?
Thanks
Sharon

 

[Barb Reinhardt]

IIRC, it does the same thing in 2003.

Barb Reinhardt

 

[Jim Rech]

Autofit does not work with merged cells. It the merged cell has only one
row you might find this macro helpful.

''Simulates row height autofit for a merged cell if the active cell..
'' is merged.
'' has Wrap Text set.
'' includes only 1 row.
''Unlike real autosizing the macro only increases row height
'' (if needed). It does not reduce row height (because another
'' merged cell on the same row may needed a greater height
'' than the active cell).
Sub AutoFitMergedCellRowHeight()
Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
Dim CurrCell As Range
Dim ActiveCellWidth As Single, PossNewRowHeight As Single
If ActiveCell.MergeCells Then
With ActiveCell.MergeArea
If .Rows.Count = 1 And .Cells(1).WrapText = True Then
Application.ScreenUpdating = False
CurrentRowHeight = .RowHeight
ActiveCellWidth = ActiveCell.ColumnWidth
For Each CurrCell In Selection
MergedCellRgWidth = CurrCell.ColumnWidth +
MergedCellRgWidth
Next
.MergeCells = False
.Cells(1).ColumnWidth = MergedCellRgWidth
.EntireRow.AutoFit
PossNewRowHeight = .RowHeight
.Cells(1).ColumnWidth = ActiveCellWidth
.MergeCells = True
.RowHeight = IIf(CurrentRowHeight > PossNewRowHeight, _
CurrentRowHeight, PossNewRowHeight)
End If
End With
End If
End Sub

 

[ShaneDevenshire]

Hi,

This is not a bug, this is how Microsoft intentionally coded it. As Jim
pointed out you can't best fit wrapped cells. However, there is a caveat to
that - if the cell is wider or taller than necessary and you apply best fit
it will size the cell down.

Why is it programmed this way? Suppose that you wrapped 20 lines of text
which was 1200 characters long and then you double-clicked that column to do
best fit, the column would become 1200 characters wide - actually the maximum
column width is 255, but you get the idea.

 

[SJHMCSE]

I see. Thank you so much, especially for the explanation.