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 (https://www.ozgrid.com/forum/index.php?thread/1229268-excel-limitation-for-vba-code/)

[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