HI All

I'm pleased you were able to sort that out between yourselves...!

I'll do it manually ....

^_^

Best Wishes


Colin




In article , Gord Dibben
<gorddibbATshawDOTca@?.? writes
Toppers

One problem with this.

If a cell in the range is blank it returns a 0

So you get 1,2,3,0,0,0,7,8,9 if A4:A6 are blank.

This function ignores blanks.

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, 13 Jul 2006 14:01:02 -0700, Toppers
m
wrote:



Try this macro:

Put this required cell;

=onelist(A1:A100) .

.. set range as required


Function onelist(ByRef rng As range) as string
bStr = ""
For Each cell In rng
bStr = bStr & Trim(Str(cell.Value)) & ","
Next
onelist = Left(bStr, Len(bStr) - 1)
End Function

"Colin Hayes" wrote:


Hi All

Hope someone can help.

I have a column of numbers going down the sheet in column A.

I need to concatenate all the numbers in each cell in the column and
separate each by a comma. This would then be displayed as a long list
all in cell B1.

I've been playing around for ages to do this - can someone put me out of
my misery?



Best Wishes