vba - every possible combination of the contents of 2 columns in excel

Question

Suppose I have 2 Columns:

1st column(contains 1000 rows):

U-0001

U-0002 

2nd column(contains 10 rows):

B01

B02

B03

Now, I want to generate two columns like this(with 10*1000 = 10000 rows):

U-0001 B01

U-0001 B02

U-0001 B03

U-0002 B01

U-0002 B02

U-0002 B03

Answer 1

this should do it:

Sub combineTwoCol()

    ' i,j,k are counters for first col, second col and the answer col in order.
    k = 1
    For i = 1 To 1000
        For j = 1 To 10
            Cells(k, "C").Value = Cells(i, "A").Value
            Cells(k, "D").Value = Cells(j, "B").Value 
            k = k + 1
        Next j
    Next i
End Sub

edited: you should notice that i assumed you have only those two columns is the file, if not change "A" and "B" to the corrosponding columns you need. and "C" and "D" to where you want the two output columns to be.

also if the 10 and 1000 are just examples and not really the values you can always find them dynamically like this:

'this return to the variable the last row in column A
LastRowColA = Range("A65536").End(xlUp).Row

and replace 1000 with LastRowColA