Microsoft Excel - Creating Combinations

Hi,
I have two columns: Orig and Dest and I need to combine them in many pairs. I need a vba code that will help me automate that.


Orig  Dest
A     X
B     Y
C    Z  
E    V
F    WW

The results should two columns like this:
Orig  Dest
A     X
A     Y
A     Z
A     V
A   W
B    X
B    Y
B     Z
B   V
B   WW
 and so on

Thanks.

Hello Cherifa,
Try:
Option Explicit

Sub Combinations()
Dim rngOne, rngTwo, rngCell1, rngCell2 As Range
Dim intCount As Integer

Set rngOne = Range("A2:A6")
Set rngTwo = Range("B2:B6")
intCount = 0
For Each rngCell1 In rngOne
For Each rngCell2 In rngTwo
Range("C2").Offset(intCount, 0).Value = rngCell1.Value
Range("C2").Offset(intCount, 1).Value = rngCell2.Value
intCount = intCount + 1
Next rngCell2
Next rngCell1

End Sub

Thanks.
What if I have more than 5 observations by column. How can I modify the code to include any number of orig and dest?

Hello Cherifa,
Try
Sub Combinations()
Dim rngOne, rngTwo, rngCell1, rngCell2 As Range
Dim intCount, intRows As Integer

Set rngOne = Range("A2").CurrentRegion
intRows = rngOne.Rows.Count
If intRows > 1 Then
    Set rngOne = rngOne.Offset(1, 0).Resize(intRows - 1, 1)
Else
    Exit Sub
End If

Set rngTwo = rngOne.Offset(0, 1)
intCount = 0
For Each rngCell1 In rngOne
For Each rngCell2 In rngTwo
Range("C2").Offset(intCount, 0).Value = rngCell1.Value
Range("C2").Offset(intCount, 1).Value = rngCell2.Value
intCount = intCount + 1
Next rngCell2
Next rngCell1

End Sub

Regards
Harry

Thanks Harry. You are the best:)