Excel-VBA: Unable to Insert Line Breaks

Question

I need to insert lines in a cell, but I am unable to insert line breaks.

For example :

line1

line2

line3

With VBA code :

             Ws.Cells(i, 2) = line1 & line2 & line3

I get :

line1 line2 line3

How can I resolve this issue?

are `line1`, `line2`, `line3` strings? without linebreaks naturally in them? — Brad, May 27 '12 at 15:48

score 15 · Accepted Answer · answered May 27 '12 at 15:48

15

Is this what you are trying?

Ws.Cells(i, 2).Value = "line1" & vbnewline & "line2" & vbnewline & "line3"

or

Ws.Cells(i, 2).Value = "line1" & vbCrLf & "line2" & vbCrLf & "line3"

EDIT: Inserted quotes as mentioned in my comments.

answered May 27 '12 at 15:48

Siddharth Rout

147,039
17
206
250

If line1 is a text then put them in quotes like "line1" & vbnewline & "line2" & vbnewline & "line3" – Siddharth Rout May 27 '12 at 15:50

score 3 · Answer 2 · answered Nov 29 '12 at 08:04

I have tested a few combinations and here are the results:

cell(a,b) = line1 & vbCrLf & line2

result:
line1**
line2

cell(a,b) = line1 & vbCr & line2

result:
line1line2

cells(a,b) = line1 & vbLf & line2

result:
line1
line2

In the above results the * denotes a blank space (I don't know why), so the vbCrLf is not recommended when you want to center the cell content horizontally. My preference goes for vbLf.

score 0 · Answer 3 · answered May 27 '12 at 15:48

0

Linebreaks in VBA are vbCrLf and you can concatenate them with the strings.

answered May 27 '12 at 15:48

Ricardo Souza

16,030
6
37
69

score 0 · Answer 4 · edited May 23 '17 at 12:31

0

Ws.Cells(i, 2) = line1 & line2 & Chr(10) & line3

As per this answer.

edited May 23 '17 at 12:31

Community

1
1

answered May 27 '12 at 15:49

Holf

5,605
3
42
63

score -2 · Answer 5 · edited May 28 '12 at 02:19

-2

FYI, you can prevent coding typos by using an easier-to-type variable name.

bx = vbCrLf

textstring = "Hello everybody!" & bx & "This is my second line!"

edited May 28 '12 at 02:19

Mark Hall

53,938
9
94
111

answered May 28 '12 at 02:17

joshoff

31
7

2

That would make your code harder to read since you are going against what the reader would expect, thus not recommended at all. – Gaijinhunter May 28 '12 at 06:31

Excel-VBA: Unable to Insert Line Breaks

5 Answers5