merged cells and wrapping text

[Rod]

I have merged a few adjacent cells in a row, then asked Excel to wrap the
text with the merged cell. It refuses.

I was originally trying to do this in code, but I now find that it can't be
done even when I try and do it manually.
(Using Office XP)


Anyone know a work-around?

cheers


Rod

 

[Guest]

Try the following code:

Sub alpha()
With Range("a1:b4")
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlTop
.WrapText = True
.MergeCells = True
End With
End Sub

Regards,

Alasdair Stirling

 

[Rod]

thanks
but this only works because you have gone down to row 4.
I don't know how much text will be in cell a1 so I just want it to increase
the row height to accommodate the text in the same way Wrap works for a
single unmerged cell.

the problem is the same even if done manually without code.
As far as I can see there is no way of measuring how many lines the text
should flow onto and adjusting the row height by code either

 

[Tom Ogilvy]

Merged cells done support Autofit.

You might pursue some code like this 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

 

[Rod]

Thanks for this it looks good

Merged cells done support Autofit.

You might pursue some code like this 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