Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

vba function: return string with newline

Tags:

excel

vba

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)...

like image 761
posop Avatar asked Jun 11 '13 20:06

posop


People also ask

How do I use vbNewLine in VBA?

After the ampersand (&) symbol, press the spacebar and get the VBA constant “vbNewLine.” After the constant “vbNewLine,” press one more time space bar and add the ampersand (&) symbol. After the second ampersand (&) symbol, type one more space character, and add the next line sentence in double quotes. We have done it.

How do I break a line in a Messagebox in VBA?

If you want to force a new line in a message box, you can include one of the following: The Visual Basic for Applications constant for a carriage return and line feed, vbCrLf. The character codes for a carriage return and line feed, Chr(13) & Chr(10).

How do you break a line in VBA?

Use the line-continuation character, which is an underscore ( _ ), at the point at which you want the line to break. The underscore must be immediately preceded by a space and immediately followed by a line terminator (carriage return) or (starting with version 16.0) a comment followed by a carriage return.


1 Answers

One line of code:

Range("B1").Formula = join(application.transpose(Range("A1:A4")),vblf)
like image 171
Dan Donoghue Avatar answered Sep 22 '22 03:09

Dan Donoghue