how do I show all variables from 3 columns?

[turtle37]

not sure how to word this one, but I have 3 columns with approx 12 different words in one column, 3 in the next, 40 in the next.
What do I need to do to display all variables/combinations which are possible???

 

[SydneyGeek]

Hi, welcome to the Board!

This assumes a couple of things:

1. Your lists are in columns A:C, starting in Row1
2. You want to output the result starting in column F
3. You won't end up with over 65536 combinations

Place the following code in a new module (Alt + F11, Insert > Module, paste the code, Alt + Q to return to Excel).
To run the code, Alt + F8 and double-click the macro name

Sub AllCombos()
    Dim nCount1 As Integer
    Dim nCount2 As Integer
    Dim nCount3 As Integer
    Dim x As Long, m As Long
    Dim i As Integer, j As Integer, k As Integer
    
    Dim vArray() As Variant
    
    nCount1 = Cells(65536, 1).End(xlUp).Row
    nCount2 = Cells(65536, 2).End(xlUp).Row
    nCount3 = Cells(65536, 3).End(xlUp).Row
    x = nCount1 * nCount2 * nCount3
    
    ReDim vArray(1 To x, 1 To 3)
    m = 1
    For i = 1 To nCount1
        For j = 1 To nCount2
            For k = 1 To nCount3
                vArray(m, 1) = Cells(i, 1)
                vArray(m, 2) = Cells(j, 2)
                vArray(m, 3) = Cells(k, 3)
                m = m + 1
            Next k
        Next j
    Next i

    Range("F1").Resize(x, 3) = vArray
End Sub

Denis

 

[turtle37]

Excellent - that's it I just tested it - thanks Denis you've saved me heaps of time!