Combinations of Ranges

  • Thread starter Thread starter Faraz A. Qureshi
  • Start date Start date
F

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
 
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
 
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
 
Back
Top