Gord, you have alleviated hours of frustration on this issue for me. Thank
you.

"Gord Dibben" wrote:

Nick

Can you work with a User Defined Function?

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 & ","
Next
ConCatRange = Left(sbuf, Len(sbuf) - 1)
End Function

You can enter a range like =ConCatRange(A:A)

The UDF will not return blank cells and when cells are inserted or deleted, the
results will reflect that.

As written, a comma is inserted between each cell value.


Gord Dibben MS Excel MVP

On Thu, 27 Jul 2006 14:27:02 -0700, Nick wrote:

I have a dynamic list entered in columns. I am trying to take one of those
columns and concatenate it into a single cell to be copied directly to a
separate form in the format required. My problem is that the list is dynamic
in both the number of records and the data. It is impractical to constantly
modify the concatenate statement that provides the final product to copy and
paste into my form. Is there any way to concatenate an entire column into
one cell like this dynamically?