We help IT Professionals succeed at work.

Create Maximum combinations in renamed sheets for specific groups only

The attached file contains code that makes combinations from data in "sheet1" B10:AG19  and puts them in sheets "Numbers_X" where "X" is the first number in B10:B19.

When the code is executed again it deletes data in sheets "NumbersX" then writes new data.

The goal here is to edit the code to maximize the number of combinations made.. The current script makes combinations using 32 numbers from "sheet1" (grey / white alternate for easier view). The current code also uses the first number as the only common number as can be seen in sheets "Numbers_X".

The additional numbers have been highlighted light and dark yellow. If it is not possible to max at 69-70 then I'll accept the maximum possible under the circumstances. Please specify the maximum possible numbers used so I can adjust the data accordingly.
CreateMaxCombinationsForSpecificGro.xlsm
Comment
Watch Question

Social distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
In the Q_28233532 Sub in this workbook you'll find this code.
For Each vntRange In Array([B10:AZ10], _
                             [B11:AZ11], _
                             [B12:AZ12], _
                             [B13:AZ13], _
                             [B14:AZ14], _
                             [B15:AZ15], _
                             [B16:AZ16], _
                             [B17:AZ17], _
                             [B18:AZ18], _
                             [B19:AZ19])

Open in new window

which you probably realize sets of the ranges of numbers that are searched. The upper limit of each of those ranges used to be "AG" rather than "AZ" and that's as much data given my version of Excel and my operating system can handle before running out of memory and stopping. If you run out of memory when you run this you'll need to experiment with changing all the AZ's to AY's or AX's etcetera until it runs without the out of memory error. On the other hand if this runs OK for you, try changing the AZ's to BA's or BB's etcetera until you get the out of memory error. I would actually start by changing the AZ's to BR's just to see if you can do them all.
29173172.xlsm

Author

Commented:

Martin,


I filled in all the blanks to ensure I did not get an errors due to missing numbers. 

Changing all the AZ's to BR's worked for me. It did all that in about three minutes time.


Impressive!

Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
See if this is faster. You'll need to change the AZ's again.

If nothing else it's 1/4 of the size of the previous workbook.
29173172a.xlsb