I have a single row of data, which will grow, which I need to merge into a
long list of text in one cell, so I can paste into another program. I have
started with the concantenate function, but I could do with a function to
automatically go down the list until there is no more data. I think I have
seen this done, but I can't remember how.
TIA

One way. Just make sure that there is an empty column between last data and
the column to fill. If putting data in col G then col F should be blank

Sub mergecolumndata()
mc = "a"
lr = Cells(Rows.Count, mc).End(xlUp).Row
For Each c In Range(Cells(2, mc), Cells(lr, mc))
lc = Cells(c.Row, mc).End(xlToRight).Column + 1
mystr = ""
For i = 1 To lc
mystr = mystr & Cells(c.Row, i) & ","
Next i
cells(c.Row, "g") = Left(mystr, Len(mystr) - 2)
Next c
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Terryrubby" wrote in message
...
I have a single row of data, which will grow, which I need to merge into a
long list of text in one cell, so I can paste into another program. I
have
started with the concantenate function, but I could do with a function to
automatically go down the list until there is no more data. I think I
have
seen this done, but I can't remember how.
TIA

Thanks for the quick reply.

I have added this macro to a shape, but it comes up with an error 1004 and
highlights the mystr = mystr & Cells(c.Row, i) & "," line.

Was I supposed to add in some information? At the moment all my data is in
column a from row 1 to 29 if this helps.

"Don Guillett" wrote:

One way. Just make sure that there is an empty column between last data and
the column to fill. If putting data in col G then col F should be blank

Sub mergecolumndata()
mc = "a"
lr = Cells(Rows.Count, mc).End(xlUp).Row
For Each c In Range(Cells(2, mc), Cells(lr, mc))
lc = Cells(c.Row, mc).End(xlToRight).Column + 1
mystr = ""
For i = 1 To lc
mystr = mystr & Cells(c.Row, i) & ","
Next i
cells(c.Row, "g") = Left(mystr, Len(mystr) - 2)
Next c
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Terryrubby" wrote in message
...
I have a single row of data, which will grow, which I need to merge into a
long list of text in one cell, so I can paste into another program. I
have
started with the concantenate function, but I could do with a function to
automatically go down the list until there is no more data. I think I
have
seen this done, but I can't remember how.
TIA

Since you didn't fully explain and you said "a single row of data", I
assumed you had info in columns a,b,c or a,b,c,d etc. and wanted to string
together into column G.
A B C D E F G
a b c BLANK a,b,c
a b c d BLANK a,b,c,d
a b c BLANK a,b,c
a b c d ff BLANK a,b,c,d,ff


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Terryrubby" wrote in message
...
Thanks for the quick reply.

I have added this macro to a shape, but it comes up with an error 1004 and
highlights the mystr = mystr & Cells(c.Row, i) & "," line.

Was I supposed to add in some information? At the moment all my data is
in
column a from row 1 to 29 if this helps.

"Don Guillett" wrote:

One way. Just make sure that there is an empty column between last data
and
the column to fill. If putting data in col G then col F should be blank

Sub mergecolumndata()
mc = "a"
lr = Cells(Rows.Count, mc).End(xlUp).Row
For Each c In Range(Cells(2, mc), Cells(lr, mc))
lc = Cells(c.Row, mc).End(xlToRight).Column + 1
mystr = ""
For i = 1 To lc
mystr = mystr & Cells(c.Row, i) & ","
Next i
cells(c.Row, "g") = Left(mystr, Len(mystr) - 2)
Next c
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Terryrubby" wrote in message
...
I have a single row of data, which will grow, which I need to merge into
a
long list of text in one cell, so I can paste into another program. I
have
started with the concantenate function, but I could do with a function
to
automatically go down the list until there is no more data. I think I
have
seen this done, but I can't remember how.
TIA

Sorry Don,

I knew what I wanted to say but it came across wrong. If I understand what
you have written, can I substitute the the word column for row in certain
lines, if not all. I haven't had a chance to try it yet, but will give it a
go this evening

I still do NOT understand your problem. Send you file to my address below if
you like.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Terryrubby" wrote in message
...
Sorry Don,

I knew what I wanted to say but it came across wrong. If I understand
what
you have written, can I substitute the the word column for row in certain
lines, if not all. I haven't had a chance to try it yet, but will give it
a
go this evening

Don,

I have emailed you the file

Thanks

"Don Guillett" wrote:


I still do NOT understand your problem. Send you file to my address below if
you like.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Terryrubby" wrote in message
...
Sorry Don,

I knew what I wanted to say but it came across wrong. If I understand
what
you have written, can I substitute the the word column for row in certain
lines, if not all. I haven't had a chance to try it yet, but will give it
a
go this evening

Makes all of col A into one cell.

Sub mergecolumndata()
mc = "a"
lr = Cells(Rows.Count, mc).End(xlUp).Row
For Each c In Range(Cells(1, mc), Cells(lr, mc))
mystr = mystr & c & ","
Next c
'MsgBox mystr
Cells(1, mc).Offset(, 1).Value = Left(mystr, Len(mystr) - 1)
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Terryrubby" wrote in message
...
Don,

I have emailed you the file

Thanks

"Don Guillett" wrote:


I still do NOT understand your problem. Send you file to my address below
if
you like.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Terryrubby" wrote in message
...
Sorry Don,

I knew what I wanted to say but it came across wrong. If I understand
what
you have written, can I substitute the the word column for row in
certain
lines, if not all. I haven't had a chance to try it yet, but will give
it
a
go this evening

Many Thanks Don,

That worked a treat

"Don Guillett" wrote:

Makes all of col A into one cell.

Sub mergecolumndata()
mc = "a"
lr = Cells(Rows.Count, mc).End(xlUp).Row
For Each c In Range(Cells(1, mc), Cells(lr, mc))
mystr = mystr & c & ","
Next c
'MsgBox mystr
Cells(1, mc).Offset(, 1).Value = Left(mystr, Len(mystr) - 1)
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Terryrubby" wrote in message
...
Don,

I have emailed you the file

Thanks

"Don Guillett" wrote:


I still do NOT understand your problem. Send you file to my address below
if
you like.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Terryrubby" wrote in message
...
Sorry Don,

I knew what I wanted to say but it came across wrong. If I understand
what
you have written, can I substitute the the word column for row in
certain
lines, if not all. I haven't had a chance to try it yet, but will give
it
a
go this evening

Glad to help

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Terryrubby" wrote in message
...
Many Thanks Don,

That worked a treat

"Don Guillett" wrote:

Makes all of col A into one cell.

Sub mergecolumndata()
mc = "a"
lr = Cells(Rows.Count, mc).End(xlUp).Row
For Each c In Range(Cells(1, mc), Cells(lr, mc))
mystr = mystr & c & ","
Next c
'MsgBox mystr
Cells(1, mc).Offset(, 1).Value = Left(mystr, Len(mystr) - 1)
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Terryrubby" wrote in message
...
Don,

I have emailed you the file

Thanks

"Don Guillett" wrote:


I still do NOT understand your problem. Send you file to my address
below
if
you like.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Terryrubby" wrote in message
...
Sorry Don,

I knew what I wanted to say but it came across wrong. If I
understand
what
you have written, can I substitute the the word column for row in
certain
lines, if not all. I haven't had a chance to try it yet, but will
give
it
a
go this evening