Excel VBA : HOW TO PRINT THE TEXT IN A CELL (like Wrap Text)

Question

after running the code it is showing the text like this "AASD FDG HJK LSDH YTWIN " in a cell but i need those words in a cell wrapped like

AASD
FDG
HJK
LSDH
YTWIN

this in one cell. what changes needs to be done to get like that for this code

Ws.Range("E" & R).Value = "AASD FDG HJK LSDH YTWIN "

score 1 · Answer 1 · answered Oct 22 '20 at 08:33

1

Your question is answered here

In your case, the solution would be:

Ws.Range("E" & R).Value = "AASD" & chr(10) & "FDG" & chr(10) & "HJK" & chr(10) & "LSDH" & chr(10) & "YTWIN "

As mentioned in linked post, this automatically sets WrapText to True.

answered Oct 22 '20 at 08:33

Salamander Krajza

236
2
7

score 1 · Accepted Answer · answered Oct 22 '20 at 08:34

To have a line break in an Excel cell, use a LineFeed character. In VBA, a constant vbLf is defined for that. Furthermore, the cell needs to have the WrapText-property set.

So you can use

With ws.Range("E" & R) 
    .WrapText = True
    .Value = "AASD" & vbLf & "FDG" & vbLf & "HJK" & vbLF & "LSDH" & vbLF & "YTWIN"
    ' Or use
    .Value = Replace("AASD FDG HJK LSDH YTWIN ", " ", vbLf) 
end With

Excel VBA : HOW TO PRINT THE TEXT IN A CELL (like Wrap Text)

2 Answers2