Excel cell data to string

[BCTjacko]

Does anybody know how to convert a column of values to a string that is separated by commas? The column is of variable length.

e.g column A contains

14
25
654
12

the result required is 14,25,654,12

I was thinking of Do Loop using a range, eg.

Do Until rngcell.Value = ""
Loop

Any help would be appreciated.

 

[ETID]

if 14 is in cell A1 then in b1 the formula is =A1

then in b2 the formula is =A2&","&B1

then fill down from there.

 

[xlbo]

Yeh but if you have 2000 rows, you're a bit screwed - 255 chars inna cell and all that

Not sure if this will help but it's the fastest way I've found of putting thousands of seperate bits of data into one place - puts them into a variant / array but will load 65536 seperate values in apprx 3 seconds
dim aHoldVar as variant
aHoldVar = Range("A1&quot.Offset(1, 0).Resize(Range("A1&quot.CurrentRegion.Rows.Count - 1, 1).Value

If you have to load to a string, something like this'll work (but it may take a while, depending on number of rows)

mStr = ""
for each c in range("A1:A" & range("A1&quot.end(xldown).row)
if mStr = "" then
mStr = c.text
else
mStr = mStr & "," & c.text
end if
next

HTH Rgds
~Geoff~

 

[BCTjacko]

Thanks for your help - they both work.