Merging cells

[KWR]

I am looking to merge a column of 630 cells into one cell with the values separated by "_,"

For example

12
13
14
15
16

would merge to: 12_,13_,14_,15_,16_,

Any thoughts on this? Im struggling to automate the macro such that is works for varying amounts of cells (500 in one sheet, 30 in another). Any help would be very much appreciated.

 

[KWR]

Any ideas?

 

[VoG]

Try something like

Sub concat()
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
Range("A" & LR + 1).Value = Join(Application.Transpose(Range("A1:A" & LR).Value), "_,")
End Sub

 

[lenze]

Sub MergeMe()
Dim m As String
Dim cl As Range
For Each cl In Selection
m = m & cl & "_,"
Next cl
m = Left(m, Len(m) - 3)
Range("$B$2") = m
End Sub

lenze

 

[KWR]

THanks for the quick response. It doesnt seem to be working with numbers stored as text, is there any way to make it work with both standard numbers and those stored as text? Thanks

 

[VoG]

With numbers stored as text

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:158px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td style=" border-width:3px; border-styleutset;border-color:#f0f0f0; ">A</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >1</td><td style="text-align:right; ">12</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">13</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">14</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">15</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">16</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >6</td><td >12_,13_,14_,15_,16</td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

 

[KWR]

nevermind. I got it working. Thanks a lot for all of the help. Just saved me a TON of time. Thanks!

 

[lenze]

Did you try the one I posted? It seems to work, but change the 3 to as 2. BTW, Peter's code also works for me

lenze