Concatenate Leave Out Blanks Add Carriage Return

Add this code to a new VBA Module within your workbook:

Please Login or Register  to view this content.

Then, if your data is in A1:A6, you can type this into B1: =ConcRange(A1:A6,CHAR(10))
Just make sure Text Wrap is on, otherwise it won't look right.

- Moo

...or a non VBA solution, with your data starting in A2, in B2

Formula:

Please Login or Register  to view this content.

copied down to last cell, with the last cell formatted with 'Wrap Text'

Glad to help, and thanks for the feedback. It's always appreciated.

- Moo

Moo,

After working with the code you provided, I realized I should have given you a tad bit more data. All my data is in column A, but I want to skip a few rows so when I use your formula, some of the text in the cell is only separated by a space instead of a carriage return. Is there a formula I can insert between your formula to make it have a carriage return instead of a space? My example is below.


Column A
Apple
Apple Pie
Blank
Banana Bread
Blank
Blueberry Muffins
SKIP
SKIP
SKIP
Turkey Sandwich
Ham Sandwich
Bologna Sandwich
SKIP
SKIP
SKIP
Cookie
Ice Cream

It looks like this in the cell.
Apple
Apple Pie
Banana Bread
Blueberry Muffins Turkey Sandwich
Ham Sandwich
Bologna Sandwich Cookie
Ice Cream

I hope that helps.

I don't know what you mean by SKIP a row... if there are blanks in the range from A1:A17 (based on your new list) it will ignore the blanks. But the formula won't SKIP rows (as in, disregard rows with values).

See my attached sheet with your latest data included.. seems to work fine.

- Moo

I guess if you want to manually skip certain cells, you could do it this way:

Formula:

Please Login or Register  to view this content.

- Moo

Originally Posted by Moo the Dog

I guess if you want to manually skip certain cells, you could do it this way:

Formula:

Please Login or Register  to view this content.

- Moo

I must have posted this when you were writing a reply.

You're welcome. Glad to help.

- Moo