EXCEL-VBA: Concatenate n rows with each line of another column-CodePudding

I am stuck with what I though was a simple action to ask excel. I need to concatenate each and every line of a column with each of the column of a second column. This is what I would like to do

Column A	Column B	Result
AA	XX	AA XX
BB	YY	AA YY
CC	ZZ	AA ZZ
	..n	BB XX
		BB YY
		BB ZZ
		CC XX
		CC YY
		CC ZZ
		AA ..n
		BB ..n
		CC ..n

I tried to look into the existing questions, but maybe I don't know which keywords to use, I can't find a good code.The closest I found is

CodePudding user response：

If column C have only a title, below code does the work:

Sub test()
    Range("A1").Select  ' Select 1st row of column A
    Selection.End(xlDown).Select  ' Move to last row in column A
    last_col_A = ActiveCell.Row  ' Get last row number in column A
    
    Range("B1").Select  ' Select 1st row of column B
    Selection.End(xlDown).Select  ' Move to last row in column B
    last_col_B = ActiveCell.Row  ' Get last row number in column A
    
    row_C = 2 ' Start from second row of column C
    For rowA = 2 To last_col_A
        For rowB = 2 To last_col_B
            Range("C" & row_C).Value = Range("A" & rowA).Value & " " & Range("B" & rowB).Value
            row_C = row_C   1  ' Increment row number
        Next
    Next        
End Sub