Merge multiple row data in one cell with delimieters
 

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

Merge multiple row data in one cell with delimieters
 

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

Merge multiple row data in one cell with delimieters
 

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

Merge multiple row data in one cell with delimieters
 

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

Merge multiple row data in one cell with delimieters
 

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

Merge multiple row data in one cell with delimieters
 

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

Merge multiple row data in one cell with delimieters
 

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

Merge multiple row data in one cell with delimieters
 

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

Merge multiple row data in one cell with delimieters
 

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

Merge multiple row data in one cell with delimieters
 

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