Vba function: returns a string with a newline

Question

First of all, new to VBA.

I am implementing this solution https://stackoverflow.com> .

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

my conclusion: Iamaboy , but I would like in one cell:

 I am a boy 

Functions do not return rows using vbNewLine , chr(10) or chr(13) ...

Answer 1

One line of code:

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

Answer 2

The answer is as tlewin said

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

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

 =ConcatenateRange(A1:A4," ") 

“But wait,” you say, “I tried it, and it doesn't 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 an instruction like [A1] = "I" & vbNewLine & "am" Excel automatically changes the formatting of the cells and enables word wrap. Word wrap is required to display lines in a cell. However, if you return a row with a line in it from UDF Excel, it does not update the format.

I can imagine two possible solutions:

• Manually enable word wrap in any cell in which you use this feature in
• (Not recommended) Save the link in Application.Caller when calling UCF ConcatenateRange, then set the call to Application.OnTime(now, "AddWordWrap") and write the AddWordWrap routine so that it uses the saved link to add the wordwrap formatting to the cell (this is because you cannot update cell format in UDF). This method is erroneous and problematic.

Answer 3

Just use the CHAR delimiter (10):

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

Answer 4

Using:

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

Answer 5

try using

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

Answer 6

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

Answer 7

I was stuck with this and it worked as soon as I used "wrap text".