Wrapping Text with VBA

[Stewart Allen]

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

 

[Ian Coates]

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.

 

[Roger]

Hi,

I also have same problem in splitting long sentence into several rows, and
add them to seperate rows of active worksheets. Could anybody help me ?

Thx!

Roger

 

[Patricia Rodilosso]

I am working on the same problem. I got nowhere trying to substitute
the vbCrlf character in the string. That seems only to work if you
display the cell contents in a text box. I think what we need to do
is find a way to code the sequence of keystrokes that enters the
carriage return/line feed into the cell for display.
So how do we code the CTRL-ALT-ENTER keystrokes?
Regards,
Pat

 

[Stewart Allen]

Thanks, I'll give that a go.

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.

have

 

[Stewart Allen]

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]

This is getting a bit deep for me. Having merged the cells, do you need to
Set MyR again? I know that Excel addresses merged cells as the top left cell
in the group, but I don't know if VBA sees it the same way. Perhaps you have
to set the range as if the cells were discreet.

Set MyR = ActiveSheet.Range("C1:M1")

It's just a guess.

Ian