You should have mentioned merged cells initially. Yes, merged cells are
totally ignored for purposes of autofitting row heights.
If you're familiar with macros this might save you a little hassle. Be sure
to read all the rules<g>:
''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
--
Jim
| Hi Jim,
|
| ...and thanks. You were thinking the right question. And your answer is
| correct for all my rows except the ones with merged cells and they are
really
| the problem.
|
| I have a couple rows with merged cells that increase in content every once
| in a while and I would like the cell's ht to increase as well. Is it
possible
| that I'm destined to manually (yuch) adjust them each time?
|
| Oh please say it isn't so.
|
| Sal
| --
|
| "Jim Rech" wrote:
|
| > Let me answer the question I think you're asking..
| >
| > In a new worksheet all rows are set to 'auto-fit' their row heights.
That
| > means if you turn on word wrap or if you increase the font size of a
cell,
| > the row's height will automatically increase to accommodate it.
However, if
| > you manually set a row's height, it no longer will auto-fit. Excel
assumes
| > that when you manually set a row height it's because that is the height
you
| > want, so it maintains it.
| >
| > To get back to autofit use Format, Row, AutoFit. A shortcut is to
| > double-click the separator between the row numbers in the left border.
| >
| > --
| > Jim
