Combinations of Ranges
 

Here is another approach you can consider...

Sub CombineRanges()
Dim X As Long, R1 As Range, R2 As Range, D1 As Range, D2 As Range
Set R1 = Range("A1:A10")
Set R2 = Range("B1:B6")
Set D1 = Range("C1")
Set D2 = Range("D1")
R2.Copy D1.Resize(R1.Count * R2.Count)
For X = D2.Row To D2.Row + R1.Count * R2.Count - 1 Step R2.Count
R1(1 + X / R2.Count).Copy Cells(X, D2.Column).Resize(R2.Count)
Next
End Sub

--
Rick (MVP - Excel)


"Faraz A. Qureshi" wrote in
message ...
Any idea of a macro of creating a combination of two ranges.

For example, if the ranges omprise of

Range 1:

Branch 1
Branch 2
Branch 3
Branch 4
Branch 5
Branch 6
Branch 7
Branch 8
Branch 9
Branch 10

Range 2:

Product 1
Product 2
Product 3
Product 4
Product 5
Product 6

The result coming out to be

A new range of two columns as:

Product 1 Branch 1
Product 2 Branch 1
Product 3 Branch 1
Product 4 Branch 1
Product 5 Branch 1
Product 6 Branch 1
Product 1 Branch 2
Product 2 Branch 2
Product 3 Branch 2
Product 4 Branch 2
Product 5 Branch 2
Product 6 Branch 2
Product 1 Branch 3
Product 2 Branch 3
Product 3 Branch 3
Product 4 Branch 3
Product 5 Branch 3
Product 6 Branch 3

and so on...

for every item of Range 1 with that of Range 2

All your help shall be highly appreciated.

Thanx in advance.

Best Regards,

Faraz A. Qureshi