TextJoin 2 columns and paste it on new worksheet in VBA excel-CodePudding

Excel Cells Results

Hi everyone. From the table above, I would like to use macro excel with a for loop and TEXTJOIN to combine the cells so that it looks like A1, B2, C3 , and paste it to a new worksheet. Do anyone has an idea on how to make this work?

CodePudding user response：

We can get the last row of both columns then extend the formula of textjoin till max of both... Something like this below... No need of for loop though...

' Input is in "Sheet1" & Output in "Sheet2"

Sub join()

Sheets("Sheet1").Activate
lRow1 = Cells(Rows.Count, 1).End(xlUp).Row
lRow2 = Cells(Rows.Count, 2).End(xlUp).Row
lRow = Application.WorksheetFunction.Max(lRow1, lRow2)

Sheets("Sheet2").Activate
Range("A1").Select
Range("A1").Formula = "=TEXTJOIN(,0,Sheet1!A1:B1)"
Selection.Copy
Range("A1:A" & lRow).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A1").Select

End Sub

Hope this Helps...