With your workbook currently open.................

Alt + F11 to open VBE

CTRL + r to open Project Explorer

Select your workbook/project and Right-clickInsertModule

Paste the UDF into that module.

Alt + q to return to the Excel window.

Enter the formula into a sheet cell.


Gord

On Wed, 9 Sep 2009 15:19:01 -0700, CSchwass
wrote:

Gord,

I am familiar with macros but not UDFs. How do I paste this to a "general
module"?

Thanks,
Chris

"Gord Dibben" wrote:

Copy/paste this UDF to a general module in your workbook.

Function ConCatRange(CellBlock As Range) As String
'for non-contiguous cells =ccr((a1:a10,c4,c6,e1:e5))
Dim Cell As Range
Dim sbuf As String
For Each Cell In CellBlock
If Len(Cell.text) 0 Then sbuf = sbuf & Cell.text & ","
Next
ConCatRange = Left(sbuf, Len(sbuf) - 1)
End Function

This UDF ignores blank cells so you can make the range quite large to
accomodate future entries in column A

=concatrange(A1:A100)

Please note: Excel will truncate the contents after about 1024 characters


Gord Dibben MS Excel MVP

On Wed, 9 Sep 2009 09:20:03 -0700, CSchwass
wrote:

I have a list like this in column A:

2345
1023
1492
2985
2902
etc

...but much longer. I want this result in one cell:

2345,1023,1492,2985,2902,etc

I will continually add to the list in Column A and I don't want to update
the formula every time for the CSV cell.

How can I do this?

Thanks,
Chris