vba function: return string with newline

Question

first of all, new to VBA.

I am implementing this solution how-to-merge-all-column-into-one-cell-in-excel[stackoverflow], but have a follow-on.

A1:I
A2:am
A3:a
A4:boy

my output is: Iamaboy but I would like in one cell:

I
am
a
boy

Functions do not seem to return strings with vbNewLine, chr(10) or chr(13)...

you need to set `wrap text` of your cell manually in Excel, too. — Kazimierz Jawor, Jun 11 '13 at 20:59

score 3 · Accepted Answer · answered Jun 01 '15 at 00:26

3

One line of code:

Range("B1").Formula = join(application.transpose(Range("A1:A4")),vblf)

answered Jun 01 '15 at 00:26

Dan Donoghue

6,056
2
18
36

score 2 · Answer 2 · answered Nov 12 '13 at 17:32

The answer is, as tlewin said

=ConcatenateRange(A1:A4,CHAR(10))

Or alternatively, using [alt-enter] you could write it like this

=ConcatenateRange(A1:A4,"
")

"But wait," you say, "I tried that and it does not work!"
Well, you see, when you manually enter something like I[alt-enter]am[alt-enter]a[alt-enter]boy in Excel, or even execute a statement such as [A1] = "I" & vbNewLine & "am" Excel automatically changes the cells formatting and turns on word wrap. Word wrap is required for linebreaks to be shown in a cell. However if you return a string with a linebreak in it from a UDF Excel does not update the format.

I can think of two possible solutions:

Manually enable word wrap on any cell you use this function in
(Not recommended) Store the reference in Application.Caller during your ConcatenateRange UDF call, then set an Application.OnTime(now, "AddWordWrap") call and write the AddWordWrap subroutine so that it uses the stored reference to add the wordwrap formatting to the cell (this is because you cannot update the cell's format in a UDF). This method is buggy and problematic.

Your additional information on word warp is spot on. This is a limitation of excel. I must live with it. — jherbold, Jun 07 '20 at 06:20

score 0 · Answer 3 · answered Jun 11 '13 at 20:59

0

Just use the CHAR(10) separator:

=ConcatenateRange(A1:A4;CHAR(10))

answered Jun 11 '13 at 20:59

Thiago Lewin

2,810
14
18

I'd definitely upvote this as it seems that it should work, but it doesn't. – Daniel Jun 11 '13 at 21:22
@DanielCook Did you try CHAR(13)? And you recalculate the worksheet (F9)? – Thiago Lewin Jun 11 '13 at 21:31

score 0 · Answer 4 · edited Nov 04 '13 at 04:40

0

Use:

=A1&"[Alt+Enter]"&A2&"[Alt+Enter]"&A3&"[Alt+Enter]"&A4

edited Nov 04 '13 at 04:40

Michael Petrotta

59,888
27
145
179

answered Nov 04 '13 at 04:18

Praveen

1

score 0 · Answer 5 · edited Aug 19 '14 at 14:40

0

try using this

Sheet1.Range("A1").Value = "sgar" & Chr(10) & "saha"

edited Aug 19 '14 at 14:40

VMAtm

27,943
17
79
125

answered Aug 19 '14 at 14:18

sagar mhatre

9
1
3

score 0 · Answer 6 · answered Dec 27 '17 at 05:39

0

Cells(row, column) = "current line" & Chr(10) & "new line"

answered Dec 27 '17 at 05:39

ILya Kuzmin

85
1
1
9

score -1 · Answer 7 · answered Jun 12 '13 at 07:16

-1

I stuck this in and it did work once I used 'wrap text'.

answered Jun 12 '13 at 07:16

dra_red

432
1
3
10

vba function: return string with newline

7 Answers7