VBA - Concatenate columns with delimiter

[Sinsation]

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.

29865

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

Hope somebody can help :)

[georgiboy]

Welcome to the forum, try the attached.

[Sinsation]

Fantastic! Thank you so much :)

[snb]

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

[georgiboy]

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.

[arnelgp]

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

[snb]

or a simple Excel formula:


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

[Tom Jones]

Another simply formula if excel has TEXTJOIN

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