VBA code to concate a range and have a ; as the divider between the values

[drop05]

I am trying to concatenate values from a range in column A from row 1 to row 81 with a "; " to be between each value in the concat, i am not sure how t to get it done in an easier way than

"=CONCATENATE(RC[-1],""; "",R[1]C[-1],""; "")"

 

[Peter_SSs]

Could you give us a small set of sample dummy data and the expected results with XL2BB?
That way we get a better idea of what you have, where it is, as well as what you want and where that is.
Say rows 1:10 instead of 1:81.

 

[drop05]

xl2bb.xlam
	A
1	128902
2	128903
3	128909
4	128911
5	128914
6	128916
7	128921
8	128967
9	128975
10	128983
Sheet1

Cell Formulas
Range	Formula
A1	A1	="128902"
A2	A2	="128903"
A3	A3	="128909"
A4	A4	="128911"
A5	A5	="128914"
A6	A6	="128916"
A7	A7	="128921"
A8	A8	="128967"
A9	A9	="128975"
A10	A10	="128983"

 

[drop05]

The range is in column A and the output would be how it is in B1

xl2bb.xlam
	A	B
1	128902	128902; 128903; 128909; 128911; 128914; 128916; 128921; 128967; 128975; 128983
2	128903
3	128909
4	128911
5	128914
6	128916
7	128921
8	128967
9	128975
10	128983
Sheet1

Cell Formulas
Range	Formula
B1	B1	=CONCATENATE(A1,"; ",A2,"; ",A3,"; ",A4,"; ",A5,"; ",A6,"; ",A7,"; ",A8,"; ",A9,"; ",A10)
A1	A1	="128902"
A2	A2	="128903"
A3	A3	="128909"
A4	A4	="128911"
A5	A5	="128914"
A6	A6	="128916"
A7	A7	="128921"
A8	A8	="128967"
A9	A9	="128975"
A10	A10	="128983"

 

[Peter_SSs]

Thanks. Do you really need vba? A simple formula will do that for you.

23 02 24.xlsm
	A	B
1	128902	128902; 128903; 128909; 128911; 128914; 128916; 128921; 128967; 128975; 128983
2	128903
3	128909
4	128911
5	128914
6	128916
7	128921
8	128967
9	128975
10	128983
Concat

Cell Formulas
Range	Formula
B1	B1	=TEXTJOIN("; ",1,A1:A10)

 

[Peter_SSs]

Do you really need vba?

If you do, try ..

Sub myConcat()
  Range("B1").Value = Join(Application.Transpose(Range("A1:A10").Value), "; ")
End Sub

 

[drop05]

If you do, try ..

Sub myConcat()
  Range("B1").Value = Join(Application.Transpose(Range("A1:A10").Value), "; ")
End Sub

i wasnt sure the formual rather than clicking each cell for the range but if i can use a range like A1:A10 and have the "; " there that would be way easier yes

 

[Peter_SSs]

t if i can use a range like A1:A10 and have the "; " there that would be way easier yes

So, did you try the TEXTJOIN formula from post #5?

 

[alansidman]

An option available to you is Power Query.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Transposed Table" = Table.Transpose(Source),
    #"Merged Columns" = Table.CombineColumns(#"Transposed Table",{"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10"},Combiner.CombineTextByDelimiter("; ", QuoteStyle.None),"Merged")
in
    #"Merged Columns"

 

[Peter_SSs]

An option available to you is Power Query.

Post #1 states that there are actually 81 rows. Would you really have to list them all individually like that?