Autofit didn't work to adjust row height to fit wrapped contents

P

Paul Lautman

nwacct wrote:

Try this:

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
 
N

nwacct

Thanks, Paul, but this is Greek to me - is your reply something I would paste
somewhere in my spreadsheet or something I need to do more "globally" to the
Excel in my PC?
 
P

Paul Lautman

nwacct said:
Thanks, Paul, but this is Greek to me - is your reply something I
would paste somewhere in my spreadsheet or something I need to do
more "globally" to the Excel in my PC?
Click to expand...

It is a Macro to be put into a Module
 
N

nwacct

Thanks, again. Can't believe I would have to go to all this trouble just to
get the row to adjust. Funny, it seems they sometimes adjust and sometimes
don't. I'll just do it manually - don't have that many times I put that much
in a cell.
 
Top