CODEWITHSUNDEEP
excelvbanewline
Farmer
Farmer

Reputation: 11003

Excel-VBA: Unable to Insert Line Breaks

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?

Upvotes: 4

Views: 25053

Answers (5)

jhfelectric
jhfelectric

Reputation: 582

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.

Upvotes: 3

joshoff
joshoff

Reputation: 31

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!"

Upvotes: -2

Holf
Holf

Reputation: 6441

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

As per this answer.

Upvotes: 0

Ricardo Souza
Ricardo Souza

Reputation: 16468

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

Upvotes: 0

Siddharth Rout
Siddharth Rout

Reputation: 149335

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.

Upvotes: 15

Related Questions