Help modifying a VBA macro code

Samgraphics

New Member
Joined
Jan 9, 2022
Messages
40
Office Version
  1. 2011
Platform
  1. MacOS
Hi, I've been using this macro code to generate all combinations from a list I provide for a while and it works great, but now I need it to do something a little different because when the combinations gets to a lot it freezes and runs out of memory. Here's the code

VBA Code:
Public result() As Variant
 
Function Combinations(rng As Range, n As Single)
Dim b As Single
 
rng1 = rng.Value
b = WorksheetFunction.Combin(UBound(rng1, 1), n)
 
ReDim result(b, n - 1)
Call Recursive(rng1, n, 1, 0, 0)
 
For g = 0 To UBound(result, 2)
     result(UBound(result, 1), g) = ""
Next g
 
Combinations = result
 
End Function
Function Recursive(r As Variant, c As Single, d As Single, e As Single, h As Single)
Dim f As Single
 
For f = d To UBound(r, 1)
    
        result(h, e) = r(f, 1)
    
        If e = (c - 1) Then
                
            For g = 0 To UBound(result, 2)
                result(h + 1, g) = result(h, g)
            Next g
            h = h + 1
        Else
            Call Recursive(r, c, f + 1, e + 1, h)
        End If
    
Next f
    
End Function

I would like to alter the code so that i can tell it where to start and end for example if it's listing all combinations that start with one I would like to be able to make it stop when it gets to two or be able to make it start at two and list only the combinations that start with two.

Please help me.

Thank you.
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Samgraphics

New Member
Joined
Jan 9, 2022
Messages
40
Office Version
  1. 2011
Platform
  1. MacOS
Sorry, I would also like to modify it to be able to list all combinations from two lists instead of one.
 
Upvote 0

Forum statistics

Threads
1,186,746
Messages
5,959,524
Members
438,430
Latest member
julieelyse

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top