Combinations of Ranges

[Faraz A. Qureshi]

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

 

[Jacob Skaria]

Hi Faraz

Try the below..

Sub Reproduce()
Dim rng1 As Range, rng2 As Range, lngRow As Long

lngRow = 1
Set rng1 = Range("A1:A6")
Set rng2 = Range("B1:B10")

For Each cell In rng1
Range("C" & lngRow).Resize(rng2.Rows.Count).Value = cell.Text
Range("D" & lngRow).Resize(rng2.Rows.Count).Value = rng2.Value
lngRow = lngRow + rng2.Rows.Count
Next
End Sub

If this post helps click Yes

 

[Rick Rothstein]

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