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