VBA - loop to last row to concatenate

[Quey15]

Hi all,

I have the below data, in column L:

COLUMN L
1
2
3 AADDYE
4 FJGLLT
5 FJJDUE
6 FJGOTR
7 ADREWS
8 FSERTT
9 MBJGFR
10 FKOORR

I want the output as per below when I run the marco to be:

"AADDYE","FLGLLT","FJJDUE", - looping until the final data row in the workbook

So cell L3 will have quote marks before and after, and then a comma - and then the same for L4 downwards etc

So, Concatenate " AADDYE " ,
and then move onto the next row and concatenate
" FJGLLT " , and so on

until you get "AADDYE","FLGLLT","FJJDUE", FJGOTR","ADREWS","FSERTT","MBJGFR","FKOORR"

THANK YOU!

 

[Fluff]

Hi & welcome to MrExcel.
Do you just want the concatenated values in L3?
Also what should happen to the values in the rest of the column?

 

[Quey15]

Hi & welcome to MrExcel.
Do you just want the concatenated values in L3?
Also what should happen to the values in the rest of the column?

So I want to do the following, does that make sense?

	L	M	N	O
3	AADDYE	"	,	=concatenate(M3,L3,M3,N3,M3,L4,M3,N3,M3,L5,M3,N3,M3,L6,M3,N3)
4	FJGLLT
5	FJJDUE
6	FJGOTR

Output in O3 will be "AADDYE","FLGLLT","FJJDUE","FJGOTR",

Ideally, I don't want to have to put the " and , in columns M and N to get the macro to work.

 

[Fluff]

Why not use a formula

Fluff.xlsm
	L	M	N
1
2
3	AADDYE		"AADDYE","FJGLLT","FJJDUE","FJGOTR"
4	FJGLLT
5	FJJDUE
6	FJGOTR
Data

Cell Formulas
Range	Formula
N3	N3	=""""&TEXTJOIN(""",""",,L3:L6)&""""

 

[Quey15]

So I want to do the following, does that make sense?

	L	M	N	O
3	AADDYE	"	,	=concatenate(M3,L3,M3,N3,M3,L4,M3,N3,M3,L5,M3,N3,M3,L6,M3,N3)
4	FJGLLT
5	FJJDUE
6	FJGOTR

Output in O3 will be "AADDYE","FLGLLT","FJJDUE","FJGOTR",

Ideally, I don't want to have to put the " and , in columns M and N to get the macro to work.

And I want this to concatenate as per the above until the last row in the workbook.

 

[Rick Rothstein]

You can do this with a formula in XL365 (see Fluff's post in Message #4), but since you asked for a VBA solution...

You do not need a loop to do what you asked for, this single line of code will do it for you...

Sub ConcatenateColumnL()
  Range("O3").Value = """" & Join(Application.Transpose(Range("L3", Cells(Rows.Count, "L").End(xlUp))), """,""") & """"
End Sub

 

[Quey15]

You can do this with a formula in XL365 (see Fluff's post in Message #4), but since you asked for a VBA solution...

You do not need a loop to do what you asked for, this single line of code will do it for you...

Sub ConcatenateColumnL()
  Range("O3").Value = """" & Join(Application.Transpose(Range("L3", Cells(Rows.Count, "L").End(xlUp))), """,""") & """"
End Sub

Why not use a formula

Fluff.xlsm
	L	M	N
1
2
3	AADDYE		"AADDYE","FJGLLT","FJJDUE","FJGOTR"
4	FJGLLT
5	FJJDUE
6	FJGOTR
Data

Cell Formulas
Range	Formula
N3	N3	=""""&TEXTJOIN(""",""",,L3:L6)&""""

AMAZING - thank you both!!

 

[Fluff]

Glad we could help & thanks for the feedback.