This works well for autofitting with content. But if I clear the content of
the cell, the row does not shrink to fit the empty cell (in other words,
revert to the default row height).
Is there code you can provide to do this?
Thanks in advance.
"JLGWhiz" wrote:
> Here is some more info from Jim Rech about merged cells and autofit.
>
> Unfortunately autofit ignores merged cells. The only workaround I know of
> is a macro that simulates autofit for row heights. Of course it's not
> automatic unless you call it from the worksheet change event.
>
> ''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 .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 Rech
> Excel MVP
>
>
>
>
> "George Lee" wrote:
>
> > I have lengthy string that I want to display in entirety. I would like to
> > pick how many cells wide to merge and based on that, have Excel calculate for
> > me how many rows I need. How is this done?
|
Similar Threads
