Using VBA to Concatenate 2 Lists

[Alex0013]

Hello Everyone,

I'm sorry if something like this has been asked before, but I've tried searching, and I can't find the specific solution I'm looking for. What I need to do is combine 2 lists of values, but not in a "typical" way that I've seen on here for other solutions. Below is essentially what I need.

I need the Sub to take the first value in column A, and concatenate each value in column B, outputting this in column C. Then go to the next cell in column A, and concatenate each value in column to this cell, outputting this in column C as well.

I'm a beginner VBA writer, so this may be easier for some, but proving to be difficult for me

I'm assuming it will require at least 2 Do Until's, and some IsEmpty's maybe, but weaving it together into a masterpiece is where I'm coming up short!


[TABLE="class: grid, width: 500, align: left"]
<TBODY>[TR]
[TD]PH1
[/TD]
[TD]PH2
[/TD]
[TD]VBAAnswer
[/TD]
[/TR]
[TR]
[TD]E60
[/TD]
[TD]E001
[/TD]
[TD]E60E001
[/TD]
[/TR]
[TR]
[TD]E94
[/TD]
[TD]E002
[/TD]
[TD]E60E002
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]E003
[/TD]
[TD]E60E003
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]E004
[/TD]
[TD]E60E004
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]E94E001
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]E94E002
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]E94E003
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]E94E004
[/TD]
[/TR]
</TBODY>[/TABLE]

Thank you in advance and please let me know if you need any additional information from me!

Alex

 

[Joe4]

Welcome to the Board!

This code should do that. I tried to document it so you could follow along and see how it works:

Sub MyConcat()


    Dim myARange As Range
    Dim myBRange As Range
    Dim cellA As Range
    Dim cellB As Range
    Dim colCRow As Long
    
    Application.ScreenUpdating = False
  
'   Capture range in column A starting on row 2
    Set myARange = Range(Range("A2"), Range("A2").End(xlDown))


'   Capture range in column B starting on row 2
    Set myBRange = Range(Range("B2"), Range("B2").End(xlDown))
    
'   Set initial row to place results in for column C
    colCRow = 2
    
'   Loop through range
    For Each cellA In myARange
        For Each cellB In myBRange
'           Place concatenated value in column C
            Cells(colCRow, "C") = cellA.Text & cellB.Text
'           Increment row counter
            colCRow = colCRow + 1
        Next cellB
    Next cellA
    
    Application.ScreenUpdating = True
    
End Sub

 

[Alex0013]

Hey Joe,

Thank you so much! This works perfectly! Thank you very much for the comment code as well, this helps a lot in my learning of VBA.

Such a quick response too!

Alex

 

[Joe4]

Your welcome!
Glad to help!