Concatenate non-empty cells

[Carlos]

If I have a range of cells (A1:A10), how do I concatenate only the non-empty
cells with carriage returns between each one?
Thanks in advance!

 

[Gord Dibben]

Carlos

Copy/paste this User Defined Function to a General Module in your workbook.

Function ConCatRange(CellBlock As Range) As String
Dim Cell As Range
Dim sbuf As String
For Each Cell In CellBlock
If Len(Cell.text) > 0 Then sbuf = sbuf & Cell.text & Chr(10)
Next
ConCatRange = Left(sbuf, Len(sbuf) - 1)
End Function

Usage is: =ConCatRange(A1:A10)

Format the cell to "Wrap Text".

If not familiar with macros and VBA, visit David McRitchie's website on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the above code in there. Save the
workbook and hit ALT + Q to return to your workbook.


Gord Dibben Excel MVP

 

[Carlos]

Exactly what I needed! Thanks again!!
-Carlos

Carlos

Copy/paste this User Defined Function to a General Module in your workbook.

Function ConCatRange(CellBlock As Range) As String
Dim Cell As Range
Dim sbuf As String
For Each Cell In CellBlock
If Len(Cell.text) > 0 Then sbuf = sbuf & Cell.text & Chr(10)
Next
ConCatRange = Left(sbuf, Len(sbuf) - 1)
End Function

Usage is: =ConCatRange(A1:A10)

Format the cell to "Wrap Text".

If not familiar with macros and VBA, visit David McRitchie's website on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the above code in there. Save the
workbook and hit ALT + Q to return to your workbook.


Gord Dibben Excel MVP

 

[Gord Dibben]

You're welcome.

Thanks for the feedback.

Gord

Exactly what I needed! Thanks again!!
-Carlos