You're quite insightful. The approach you describe was
developed by Jim Rech some time back and as far as I know
is the method of choice. I wrote a variation of Jim's code
as shown below. If you decide to use it, change the cell
references to suit. Note that the Wraptext property of the
merged ranges must be set to True. The code assumes that
the merged cells are within the range A1:C10 with only the
columns merged (i.e. A1:C1, A2:C2, A3:C3 etc. merged).
Paste to the worksheet code module.
Private Sub Worksheet_SelectionChange(ByVal Target As
Excel.Range)
Dim RowHt As Single, MergeWidth As Single
Dim C As Range, AutoFitRng As Range
Dim CWidth As Single, NewRowHt As Single
Static OldRng As Range
On Error Resume Next
If Target.Count > 3 Then Exit Sub 'Change to suit
If OldRng Is Nothing Then _
Set OldRng = Range("A1").MergeArea
Set AutoFitRng = Range("A1:C10")
If Not Intersect(OldRng, AutoFitRng) Is Nothing Then
Application.ScreenUpdating = False
With OldRng
RowHt = .RowHeight
CWidth = .Cells(1).ColumnWidth
For Each C In OldRng
MergeWidth = C.ColumnWidth + MergeWidth
Next
.MergeCells = False
.Cells(1).ColumnWidth = MergeWidth
.EntireRow.AutoFit
NewRowHt = .RowHeight
.Cells(1).ColumnWidth = CWidth
.MergeCells = True
.RowHeight = NewRowHt
End With
Application.ScreenUpdating = True
End If
Set OldRng = Target
End Sub
Regards,
Greg
-----Original Message-----
I want to set the row height for a row containing merged
cells with long text that wraps.
Autoheight doesn't work.
The only way I can think to do it is to demerge the
cells, select the range, format for centre across
selection, autofit. At this point I would note the row
height and then merge the cells and set the height to that
noted.
