Kalpesh
03-17-2021, 03:30 AM
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
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