Yes the merging of the cells worked but one more problem has surfaced, how
to automatically change the row height. The bottom of my code is now:

MyR.Cells = Temp
MyR.VerticalAlignment = xlVAlignTop
Range(Cells(1, 3), Cells(1, 11)).MergeCells = True
MyR.WrapText = True
MyR.Rows.AutoFit <<< This line doesn't work

How do I get the row to automatically adjust its height to display all the
text when wrapping?

Thanks
Stewart


"Ian Coates" wrote in message
...
If there's no problem with the txt overlapping some of the following
columns, you could merge C1:K1 (or whatever suits the layout) and then
allow
the text to wrap.

"Stewart Allen" wrote in message
...
Hi there,

I'm trying to split long text so it doesn't go off the page sideways and
doesn't wrap inside the cell so that the cell only displays about 10
characters sideways and the rest of the text reads downwards. Type a
sentence of about 150 character into cell C1 and the text will display
to
the O or P column. When you turn on the warp text for cell C1, the cell
increases in height with all the text in the cell.

What I'm after is split the text with VBA at around the 90 - 100
character
mark and drop the rest of the text below the first line but still in the
same cell. I could split the text and place the second line in the cell
below C2 but really want to split the text and keep it inside the same
cell.

The code I've used for testing is:
Dim str as String
Dim Temp as String
Dim intPos as Integer
Dim intLen as Integer
Dim MyR as Range

intLen = Len(str)
For intPos = 1 To intLen
If intPos Mod 50 = 0 Then
Temp = Temp & vbCrLf
Temp = Temp & Mid(str, intPos, 1)
Else
Temp = Temp & Mid(str, intPos, 1)
End If
Next

Set MyR = ActiveSheet.Range("C1")
MyR = Temp

I've used 50 at the cut off for testing and insert a carriage return and
line feed every 50 characters. All this does is insert a square
character
every 50 characters but still displays the text on one line. If I add
the
code "MyR.WrapText = True", all the text wraps inside the cell but I
have
to
resize the cell width to display the text how I like. I don't want to
resize
the cell because this will distort the contents in the cell above.

Any suggestions?

Thanks
Stewart