VBA - Concatenate columns with delimiter

Hello everybody! :)

I have a question for a VBA code, and as a complete beginner in the world of VBA, I have no idea where to start.

My goal is to concatenate some columns, the number will vary, with the delimiter " > " - no quotes, in the first cell of the row.
I will be doing this for an unknown number of rows down, often several hundreds or thousands.

Below are two pictures of the idea, the first showing the concept. I have a set of columns, and these need to be combined in the first cell of each row, in this case the empty A-rows.

Attachment 29865

The result should be like this, with all columns combined, separated by the delimiter >.
Attachment 29866

Hope somebody can help :)

Welcome to the forum, try the attached.

Fantastic! Thank you so much :)

Code:

---

Sub M_snb()
    Cells(1).Resize(3000) = [B1:B3000 & if(c1:c3000="","",">" & c1:c3000) & if(d1:d3000="","",">" & d1:d3000) & if(e1:e3000="","",">" & e1:e3000) & if(f1:f3000="","",">" & f1:f3000) & if(g1:g3000="","",">" & g1:g3000)]
End Sub

---

Quote:

---

Originally Posted by snb

Code:

---

Sub M_snb()
    Cells(1).Resize(3000) = [B1:B3000 & if(c1:c3000="","",">" & c1:c3000) & if(d1:d3000="","",">" & d1:d3000) & if(e1:e3000="","",">" & e1:e3000) & if(f1:f3000="","",">" & f1:f3000) & if(g1:g3000="","",">" & g1:g3000)]
End Sub

---

---

Why the 3000?

Why not make the code dynamic as the TS has stipulated they do not know the row/ column count.

you can also create a User-Defined function and use it in your worksheet.

or a simple Excel formula:

PHP Code:

---

=SUBSTITUTE(TRIM(B1 &" " &C1 & " " & D1& " " & E1& " " &F1& " " &G1);" ";">") 


---

Another simply formula if excel has TEXTJOIN

=TEXTJOIN(">",1,B1:M1) in A1 and drag down...