Im not sure if this one helps you out and it is also not so dynamic as your macro still I think it will do the job. I did a test and it took about 11 minutes to get 1 million combinations on a machine with 2800 Athlon processor
wich is about 1430 minutes (approx 24 hours)
regards,
Jeroen
Dim i, j, k, l, m, n, o, p, q, r, s, t As Long
Dim RowsA, RowsB, RowsC, RowsD, RowsE, RowsF, RowsG, RowsH, RowsI, RowsJ, RowsK, Rw As Long
Dim MyCol1, MyCol2, MyCol3, MyCol3, MyCol4, MyCol5, MyCol6, MyCol7, MyCol8, MyCol9, MyCol10, MyCol11 As Collection
Dim strtime, Endtime, strtRow As Long
Dim ws1, ws2 As Worksheet
Dim startTime As Long
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Set ws1 = Sheets("Combinations")
startTime = Now
'Set ws2 = Sheets("Target")
strtRow = 2 'define row to start
RowsA = Application.CountA(ws1.Range("2:2"))
RowsB = Application.CountA(ws1.Range("3:3"))
RowsC = Application.CountA(ws1.Range("4:4"))
RowsD = Application.CountA(ws1.Range("5:5"))
RowsE = Application.CountA(ws1.Range("6:6"))
RowsF = Application.CountA(ws1.Range("7:7"))
RowsG = Application.CountA(ws1.Range("8:8"))
RowsH = Application.CountA(ws1.Range("9:9"))
RowsI = Application.CountA(ws1.Range("10:10"))
RowsJ = Application.CountA(ws1.Range("11:11"))
RowsK = Application.CountA(ws1.Range("12:12"))
'RowsB = ws1.Cells(strtRow + 1, 2).End(xlUp).Row
'RowsC = ws1.Cells(strtRow + 2, 3).End(xlUp).Row
Set MyCol1 = New Collection
Set MyCol2 = New Collection
Set MyCol3 = New Collection
Set MyCol4 = New Collection
Set MyCol5 = New Collection
Set MyCol6 = New Collection
Set MyCol7 = New Collection
Set MyCol8 = New Collection
Set MyCol9 = New Collection
Set MyCol10 = New Collection
Set MyCol11 = New Collection
For i = 2 To RowsA
MyCol1.Add Cells(strtRow, i)
Next
For i = 2 To RowsB
MyCol2.Add Cells(strtRow + 1, i)
Next
For i = 2 To RowsC
MyCol3.Add Cells(strtRow + 2, i)
Next
For i = 2 To RowsD
MyCol4.Add Cells(strtRow + 3, i)
Next
For i = 2 To RowsE
MyCol5.Add Cells(strtRow + 4, i)
Next
For i = 2 To RowsF
MyCol6.Add Cells(strtRow + 5, i)
Next
For i = 2 To RowsG
MyCol7.Add Cells(strtRow + 6, i)
Next
For i = 2 To RowsH
MyCol8.Add Cells(strtRow + 7, i)
Next
For i = 2 To RowsI
MyCol9.Add Cells(strtRow + 8, i)
Next
For i = 2 To RowsJ
MyCol10.Add Cells(strtRow + 9, i)
Next
For i = 2 To RowsK
MyCol11.Add Cells(strtRow + 10, i)
Next
Rw = 1
col = 4
With ActiveSheet
For Each j In MyCol1
For Each k In MyCol2
For Each l In MyCol3
For Each m In MyCol4
For Each n In MyCol5
For Each o In MyCol6
For Each p In MyCol7
For Each q In MyCol8
For Each r In MyCol9
For Each s In MyCol10
For Each t In MyCol11
'.Cells(Rw, col) = j & k & l
strValue = j & k & l & m & n & o & p & q & r & s & t
Debug.Print strValue
Rw = Rw + 1
If Rw = 1000000 Then
Endtime = Now
'MsgBox Format(Endtime - startTime, "hh:mm:ss")
End If
Next t
Next s
Next r
Next q
Next p
Next o
Next n
Next m
Next l
Next k
Next j
End With
Set MyCol1 = Nothing
Set MyCol2 = Nothing
Set MyCol3 = Nothing
Set MyCol4 = Nothing
Set MyCol5 = Nothing
Set MyCol6 = Nothing
Set MyCol7 = Nothing
Set MyCol8 = Nothing
Set MyCol9 = Nothing
Set MyCol10 = Nothing
Set MyCol11 = Nothing
Endtime = Now
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
MsgBox Format(Endtime - startTime, "hh:mm:ss")
End Sub
1: 2: 3: 4: 5: 6: 7: 8: 9: 10: 11: 12: 13: 14: 15: 16: 17: 18: 19: 20: 21: 22: 23: 24: 25: 26: 27: 28: 29: 30: 31: 32: 33: 34: 35: 36: 37: 38: 39: 40: 41: 42: 43: 44: 45: 46: 47: 48: 49: 50: 51: 52: 53: 54: 55: 56: 57: 58: 59: 60: 61: 62: 63: 64: 65: 66: 67: 68: 69: 70: 71: 72: 73: 74: 75: 76: 77: 78: 79: 80: 81: 82: 83: 84: 85: 86: 87: 88: 89: 90: 91: 92: 93: 94: 95: 96: 97: 98: 99: 100: 101: 102: 103: 104: 105: 106: 107: 108: 109: 110: 111: 112: 113: 114: 115: 116: 117: 118: 119: 120: 121: 122: 123:





by: GrahamSkanPosted on 2009-01-31 at 02:00:52ID: 23516113
You have a routine to called CopyArray which you use to expand an array into another which is can contain one more item. It copies each item one-by-one. That must be taking a lot of time.
You have a comment to say that Redim Preserve doesn't work. I am surprised about that. It always works for me. If you have found a circumstance where it fails, you could use an array of sufficient size to start with, and use a variable to mark the current highest effective index.
If you can't calculate the final size, you could merge the two techniques, and use your CopyArray routine to increase the array size by, say, 1000 items at a time. That way the slow item-by-item copy would only occur 0.1% of the previous number of times.