Resolve empty cell results in Create Combinations Script

Here's a solution to the problem, but I believe you are making a mistake when you don't attach your most recent workbook. The reason is that the workbook you attached does not contain code that allows the deletion of sets that are not valid as does the workbook prior to this one. I know that the problem you are trying to fix has nothing to do with that, but if you did try to run the Q_28233532 sub in that workbook it would fail because it won't have the fix I just made. To make a long story short, you should just have one workbook.
29173484.xlsb

 if you did try to run the Q_28233532 sub in that workbook it would fail

I agree.

To rectify, I need know how to rename each script? This way I can remember which one to run based on what I step I need.

I've used the properties dialog box to rename but when I view macros the old name is shown.

Here is where I need the name to change.

This is the most recent of the remove invalid decis file I have.

RemovesBothDecisandAndRunsFaster_2-21-2020.xlsb

You can show me how to put both into one workbook here or do it for me, if it's complicated.

Try this version. In it you'll find two buttons on Sheet1 that will run code in the workbook. Hopefully the button captions are self-explanitary but I can change them if you want me to. If you ever want to know which macro is run by a button then right-click on the button, choose 'Assign Macro...' and a window like this will pop up. I've highlighted the name of the macro in yellow. If you have more than one workbook open you should change the 'Macros In:' drop-down to say 'This Workbook'.

A question just occurred to me and that is that after you click the 'Create Sets' button, will you always want to click the 'Remove Invalid' button? If so I can remove the latter and have 'Create Sets' do both.
29173484a.xlsb

Martin,

I'd rather keep them separate to aid in troubleshooting. 

When I run the "create sets" button I only get up to "3-9" combinations. I have changed "ZA" to "BR" and I don't see any other variables that need updating.

The code in the file I attached in the original post (above) makes all combinations up to the last number possible.

 Please advise.

In order for me to not get an 'out of memory' error when I click 'Create Sets' I have to limit the number of columns on Sheet1 that the code looks at. When I make the range B:AH it runs fine and it produces Numbers_3, 4, 7, 8, 10, 11, 17, 18, 3 sheets as it should so I don't understand what you mean when you say "I only get up to "3-9" combinations".

In order for me to not get an 'out of memory' error when I click 'Create Sets' I have to limit the number of columns 

I understand that. Which is why I changed the variables to max numbers siBCE my PC can do so without running out of memory. BTW. If all you need is more memory, I may have some to give you. Send me the specs, via private message or email.

 I don't understand what you mean when you say "I only get up to "3-9" combinations".

The picture I posted shows the last few combinations in the numbers_3 group, after running the script. The script should continue until the 60’s and end with 70 in the last cell to the right. This means the code is not making all of the combinations that are possible.

When create sets in 29173484a.xlsb changing the ranges to look like this

 

  For Each vntRange In Array([B10:AX10], _
                             [B11:AX11], _
                             [B12:AX12], _
                             [B13:AX13], _
                             [B14:AX14], _
                             [B15:AX15], _
                             [B16:AX16], _
                             [B17:AX17], _
                             [B18:AX18], _
                             [B19:AX19])

Select allOpen in new window

it produces Numbers_3, 4, 7, 8, 10, 11, 17, 18, 3 sheets and I get this so I don't know what's going on.

I've reviewed the code for the code that creates combinations for both and there are line differences between them.

You might be able to duplicate the error I am getting by clearing cells AX10, AX11, AX12, AX13, etc. so you have at least one blank cell in the array. Then you may be able to duplicate what I am getting after running  29173484a.xlsb

I did as you suggested no saw no difference. Here's a workbook with my ranges where I went even further.
29173484b.xlsb

Martin,

Ran all the codes. After adding both codes in the same workbook the results change. Here's what I get after running 29173484b.xlsb

This incorrect output occurs after adding the "RemoveInvalidDecis" to the "CreateCombinations" script. I know this because I have made several attempts and I get the same results. However, is I run the "RemoveInvalidDecis" script separately (meaning in a different file) it seems to work as intended.

P.S.  I see three modules instead of the two I expected. Please remove any modules not needed to avoid confusion.

Your terminology is incorrect; there is just one module, Module1. Module1 contains one Sub,  CreateCombinations and two macros, DriveCombinationCreation and RemoveInvalidDecis. The difference between a Sub and a macro is a macro may be called from a worksheet and a Sub may not. Everything in the code is needed. When the 'Create Sets' button is clicked it executes DriveCombinationCreation, which in turn calls CreateCombinations for each range on Sheet1. When the 'Remove Invalid' button is clicked it executes the RemoveInvalidDecis macro,

You seem to be saying that you are modifying the code, and you should not generally do that. You also should not be executing the code directly. Instead you should limit yourself to just clicking one of the buttons.

With all that in mind, using the 29173484b.xlsb workbook I uploaded (unmodified). change the ranges in DriveCombinationCreation to encompass B:BR and click 'Create Sets'. Do you get the expected results.

The 29173484b.xlsb workbook you uploaded (unmodified), yields same results as above. Even if I do not change AX to BR.

BTW. I also looked at Sheets 4-18 and each yields numbers  beyond the first number in the combination. See below for sample.

Can you explain us what this algorythm is supposed to do ?
There might be better ways to do it.

I understood up to the "Get the number of combinations" part. Beyond that, it is nebulous (Something with cartesian products ?).

Side notes (so much to say in such little piece of code …):
- Use meaningfull variables and function names.
- Give up hungarian notation, it provides nothing usefull.
- Fully qualify your variables, to avoid potential conflicts.
- Be explicit rather than implicit.
- Do not rely on "Active" objects, they are unpredictable.
- Disable warnings and errors for as little time as possible (best is one instruction).
- Your code break the SRP (Single Responsibility Principle), and should be broken into several smaller functions that do one thing, and do it well.
- Get rid of those performances ehencer instructions, only add them when your code produce the expected result.
- Do not store the err object members for the sole purpose of displaying it.
- Working with collection might be easyer than arrays, since collections auto expand.