David

A small problem with your UDF..............

If any cells are blank in the A1:A10 range you get extra commas.

I.e. a,b,c,,,g,h,i,j

This modified UDF does not have that problem.

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


Gord Dibben MS Excel MVP


On Thu, 17 Aug 2006 10:41:02 -0700, David Billigmeier
wrote:

You can create a UDF to perform this. To do this follow these steps:

1) Alt-F11
2) In the left pane right click on your sheet name and choose <Insert<Module
3) Paste the following code:

Function concat(rng As Range)
For Each rng In rng
temp = temp & rng & ", "
Next
concat = Left(temp, Len(temp) - 2)
End Function

4) Then you can use the function concat() within your sheet (e.g.
=concat(A1:A10))