Sizing row height automatically
 

I'm trying to automatically resize the row height after merging cells in a
row and wrapping the text. Is there a way to do this.

My code so far is:
ActiveSheet.Range(.Cells(3, 2), .Cells(3, 5)).MergeCells = True
ActiveSheet.Cells(3, 2).WrapText = True

The data being inserted into this range is being pulled from an Access text
field so I need the row height to automatically adjust depending on how much
data is extracted. If I don't merge the cells the row will automatically
adjust its height but won't if they are merged.

Stewart

Sizing row height automatically
 

Autosize doesn't work for merged cells.

You can adapt this code previously posted by Jim Rech:

Jim Rech

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

--
Regards,
Tom Ogilvy

"Stewart Allen" wrote in message
...
I'm trying to automatically resize the row height after merging cells in a
row and wrapping the text. Is there a way to do this.

My code so far is:
ActiveSheet.Range(.Cells(3, 2), .Cells(3, 5)).MergeCells = True
ActiveSheet.Cells(3, 2).WrapText = True

The data being inserted into this range is being pulled from an Access
text
field so I need the row height to automatically adjust depending on how
much
data is extracted. If I don't merge the cells the row will automatically
adjust its height but won't if they are merged.

Stewart

Sizing row height automatically
 

Thanks for that.


"Tom Ogilvy" wrote in message
...
Autosize doesn't work for merged cells.

You can adapt this code previously posted by Jim Rech:

Jim Rech

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

--
Regards,
Tom Ogilvy

"Stewart Allen" wrote in message
...
I'm trying to automatically resize the row height after merging cells in
a
row and wrapping the text. Is there a way to do this.

My code so far is:
ActiveSheet.Range(.Cells(3, 2), .Cells(3, 5)).MergeCells = True
ActiveSheet.Cells(3, 2).WrapText = True

The data being inserted into this range is being pulled from an Access
text
field so I need the row height to automatically adjust depending on how
much
data is extracted. If I don't merge the cells the row will automatically
adjust its height but won't if they are merged.

Stewart