Excel Maximum Rows

[Kalpesh]

I am using following codes to generate combination of 5 numbers from 45 numbers. Because Excel only can handle 1048576 rows, it gives me an error message (Run-time error '1004': Method 'Range' of object'_Global' failed). Is there anything else I can do to resolve the issue?

Sub GenerateCombo()
    'Ref PCG
    Dim rRng As Range, p
    Dim vElements, lRow As Long, vresult As Variant
    Set rRng = Range("A1", Range("A1").End(xlDown))
    p = 5
    vElements = Application.Index(Application.Transpose(rRng), 1, 0)
    ReDim vresult(1 To p)
    Call CombinationsNP(vElements, CInt(p), vresult, lRow, 1, 1)
End Sub
 
Sub CombinationsNP(vElements As Variant, p As Integer, vresult As Variant, lRow As Long, iElement As Integer, iIndex As Integer)
     Dim i As Integer
     For i = iElement To UBound(vElements)
      vresult(iIndex) = vElements(i)
    
     If iIndex = p Then
            lRow = lRow + 1
            Range("B" & lRow) = Join(vresult, ", ")
            Range("C" & lRow).Resize(, p) = vresult 'Multi column Result
    Else

 Call CombinationsNP(vElements, p, vresult, lRow, i + 1, iIndex + 1)

 End If
  Next i
End Sub

[snb]

to generate combination of 5 numbers from 45 numbers

Why would you ?

[Kalpesh]

Why would you ?

To generate combination.

[snb]

Do you like circles ?

[rollis13]

Adding link for crosspost: LINK

[Paul_Hossler]

To me the macros seemed more complicated that they had to be

This is another way

Option Explicit


Sub GenerateCombo()
    Dim r As Range
    Dim v As Variant
    Dim n1 As Long, n2 As Long, n3 As Long, n4 As Long, n5 As Long
    Dim rowCount As Long, colCount As Long
    Dim N As Long
    
    With ActiveSheet
        Set r = .Range("A1")
        Set r = Range(r, r.End(xlDown))
        
        v = Application.WorksheetFunction.Transpose(r)
        N = UBound(v)
        
        rowCount = 1
        colCount = 3
        
        For n1 = 1 To N - 4
            For n2 = n1 + 1 To N - 3
                For n3 = n2 + 1 To N - 2
                    For n4 = n3 + 1 To N - 1
                        For n5 = n4 + 1 To N
                            .Cells(rowCount, colCount).Value = v(n1) & "," & v(n2) & "," & v(n3) & "," & v(n4) & "," & v(n5)
                            rowCount = rowCount + 1
                            If rowCount = 100001 Then
                                rowCount = 1
                                colCount = colCount + 1
                                Application.StatusBar = v(n1) & "," & v(n2) & "," & v(n3) & "," & v(n4) & "," & v(n5)
                                DoEvents
                            End If
                        Next n5
                    Next n4
                Next n3
            Next n2
        Next n1
    End With
    
    Application.StatusBar = False
    
    MsgBox "Done"
    
End Sub

[snb]

https://www.snb-vba.eu/VBA_Recursion_en.html