Re: Merge multiple row data in one cell with delimieters by Don
Don
Sun May 11 10:29:57 CDT 2008
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
dguillett1@austin.rr.com
"Terryrubby" <Terryrubby@discussions.microsoft.com> wrote in message
news:FFEDCC2A-9054-48C4-9FB3-50D58A322516@microsoft.com...
> 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
>> dguillett1@austin.rr.com
>> "Terryrubby" <Terryrubby@discussions.microsoft.com> wrote in message
>> news:4C099113-A9AA-4533-899A-50F13E02D8BF@microsoft.com...
>> >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
>>
>>