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

--
nwacct

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

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?
--
nwacct


"Paul Lautman" wrote:

> 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
>
>
>

nwacct wrote:
> 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?
>
>> 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

It is a Macro to be put into a Module

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.
--
nwacct


"Paul Lautman" wrote:

> nwacct wrote:
> > 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?
> >
> >> 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
>
> It is a Macro to be put into a Module
>
>
>