VBA, formula to the last row

[Patrice74]

Hi everybody,

I need a VBA code to concanate cells with the sign "|" to the last row.
Exemple with only 4 rowsbut i ca be more :
Column A:

30602
70012
70311
85244

The result should be i cell B1 : 30602|70012|70311|85244

Thanks i advance
Patrice

 

[Fluff]

How about

Sub Patrice74()
   Dim Ary As Variant
   
   Ary = Range("A1", Range("A" & Rows.Count).End(xlUp)).Value2
   Range("B1").Value = Join(Application.Transpose(Ary), "|")
End Sub

 

[mohadin]

Hi
What about

Sub conts()
     Range("b1") = Join(Application.Transpose(Range("a1:a" & Cells(Rows.Count, 1).End(xlUp).Row)), "|")
End Sub

 

[Patrice74]

Hi Fluff
Thank you very much, it's works perfectly. Have a nice day

 

[Patrice74]

Thank you Mohadin Have a nice day

 

[Fluff]

Glad we could help & thanks for the feedback

 

[mohadin]

You are well come and thank you both