Combine two ranges with different numbers of rows and columns

[Ciprian]

Hi,

I have two different ranges with different numbers of rows and columns. I
have to combine in a single range each row from the first range with each row
from the second range. I provide hereunder an example. Any idea? Thank you!

Range 1
A B C
D E F
G H I
J K L

Range 2

1 2 3 4
5 6 7 8

Result

A B C 1 2 3 4
A B C 5 6 7 8
D E F 1 2 3 4
D E F 5 6 7 8
G H I 1 2 3 4
G H I 5 6 7 8
J K L 1 2 3 4
J K L 5 6 7 8

 

[Jacob Skaria]

Try the below code...Assume that the two ranges mentioned are named ranges...

Sub Macro()

Dim lngRow As Long
Dim rng1 As Range, rng2 As Range
Dim rngRow1 As Range, rngRow2 As Range

Set rng1 = Range("myrange1")
Set rng2 = Range("myrange2")

For Each rngRow1 In rng1.Rows
For Each rngRow2 In rng2.Rows
lngRow = lngRow + 1
Sheets("Sheet2").Range("A" & lngRow).Resize(1, _
rngRow1.Columns.Count) = rngRow1.Value
Sheets("Sheet2").Range("A" & lngRow).Offset(, _
rngRow1.Columns.Count).Resize(1, rngRow2.Columns.Count) = rngRow2.Value
Next
Next

End Sub

 

[Rick Rothstein]

Here is another macro for you to consider (less looping)...

Sub CombineRanges()
Dim X As Long, Rng1 As Range, Rng2 As Range, StartCell As Range
Set Rng1 = Range("Range1")
Set Rng2 = Range("Range2")
Set StartCell = Range("A1")
Rng2.Copy StartCell.Offset(, Rng1.Columns.Count).Resize( _
Rng1.Rows.Count * Rng2.Rows.Count, Rng2.Columns.Count)
For X = 1 To Rng2.Rows.Count * Rng2.Rows.Count
Rng1.Rows(X).Copy StartCell.Offset((X - 1) * _
Rng2.Rows.Count).Resize(Rng2.Rows.Count)
Next
End Sub

Like Jacob's macro, I'm assuming you named the two ranges that you want to
combine as indicated. Of course, you can set specific ranges, rather than
named ranges, in the two Set statements if desired.