VBA - loop to last row to concatenate

Quey15

New Member
Joined
Jun 28, 2022
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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!
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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?
 
Upvote 0
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?

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

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.
 
Upvote 0
So I want to do the following, does that make sense?

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

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.
 
Upvote 0
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...
VBA Code:
Sub ConcatenateColumnL()
  Range("O3").Value = """" & Join(Application.Transpose(Range("L3", Cells(Rows.Count, "L").End(xlUp))), """,""") & """"
End Sub
 
Upvote 0
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...
VBA Code:
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
LMN
1
2
3AADDYE"AADDYE","FJGLLT","FJJDUE","FJGOTR"
4FJGLLT
5FJJDUE
6FJGOTR
Data
Cell Formulas
RangeFormula
N3N3=""""&TEXTJOIN(""",""",,L3:L6)&""""
AMAZING - thank you both!!
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top