vba to generate all combinations, no repeat, from a range?

[excelNewbie22]

hi,
i googled quite a lot, found a lot but not what i'm looking for,
i need a simple macro to generate an x numbers out of a range,
so i can edit it each time per my need,
with no repeation

for example
range will be a2:h2 (or more or less)
numbers of combinations 5 (or more or less)
if possible with hyphens seperate between i.e 1-2-3-4-5
all results in one column

can you please help?

New Microsoft Excel Worksheet.xlsx
	A	B	C	D	E	F	G	H	I	J
1									of 6	of 5
2	1	2	3	4	5	6	7	8	1-2-3-4-5-6	1-2-3-4-5
3									1-2-3-4-5-7	1-2-3-4-6
4									1-2-3-4-5-8	1-2-3-4-7
5									1-2-3-4-6-7	1-2-3-4-8
6									1-2-3-4-6-8	1-2-3-5-6
7									1-2-3-4-7-8	1-2-3-5-7
8									1-2-3-5-6-7	1-2-3-5-8
9									1-2-3-5-6-8	1-2-3-6-7
10									1-2-3-5-7-8	1-2-3-6-8
11									1-2-3-6-7-8	1-2-3-7-8
12									1-2-4-5-6-7	1-2-4-5-6
13									1-2-4-5-6-8	1-2-4-5-7
14									1-2-4-5-7-8	1-2-4-5-8
15									1-2-4-6-7-8	1-2-4-6-7
16									1-2-5-6-7-8	1-2-4-6-8
17									1-3-4-5-6-7	1-2-4-7-8
18									1-3-4-5-6-8	1-2-5-6-7
19									1-3-4-5-7-8	1-2-5-6-8
20									1-3-4-6-7-8	1-2-5-7-8
21									1-3-5-6-7-8	1-2-6-7-8
22									1-4-5-6-7-8	1-3-4-5-6
23									2-3-4-5-6-7	1-3-4-5-7
24									2-3-4-5-6-8	1-3-4-5-8
25									2-3-4-5-7-8	1-3-4-6-7
26									2-3-4-6-7-8	1-3-4-6-8
27									2-3-5-6-7-8	1-3-4-7-8
28									2-4-5-6-7-8	1-3-5-6-7
29									3-4-5-6-7-8	1-3-5-6-8
30										1-3-5-7-8
31										1-3-6-7-8
32										1-4-5-6-7
33										1-4-5-6-8
34										1-4-5-7-8
35										1-4-6-7-8
36										1-5-6-7-8
37										2-3-4-5-6
38										2-3-4-5-7
39										2-3-4-5-8
40										2-3-4-6-7
41										2-3-4-6-8
42										2-3-4-7-8
43										2-3-5-6-7
44										2-3-5-6-8
45										2-3-5-7-8
46										2-3-6-7-8
47										2-4-5-6-7
48										2-4-5-6-8
49										2-4-5-7-8
50										2-4-6-7-8
51										2-5-6-7-8
52										3-4-5-6-7
53										3-4-5-6-8
54										3-4-5-7-8
55										3-4-6-7-8
56										3-5-6-7-8
57										4-5-6-7-8
test

 

[johnnyL]

Correct, this was for single numbers. How about:

Dim ar, a, sq, x As Long

Sub jec()
 ar = [transpose(transpose(A2:H2))]
 a = Application.InputBox("Combin of...", , , , , , , 1)
 ReDim sq(Application.Combin(UBound(ar), a), 0)
 x = 0
 
 Application.ScreenUpdating = False
 
 Gen_comb "", -1, UBound(ar) - a
    
 With Cells(2, 13)
   .CurrentRegion.ClearContents
   .Resize(x) = sq
 End With
End Sub

Sub Gen_comb(xStr As String, y As Long, n As Long)
 Dim sp, j As Long
 For j = y + 1 To n
    sp = xStr & ar(j + 1)
    If UBound(Split(sp, "-")) = a - 1 Then
      sq(x, 0) = sp
      x = x + 1
    Else
      Gen_comb xStr & ar(j + 1) & "-", j, n + 1
   End If
 Next
End Sub

That version is admitingly close to 25% faster than the code I submitted. NICE!

 

[JEC]

Thanks

 

[excelNewbie22]

johnny,
can you please update the macro to ignore blank cells?
like if i set a2:h2
and d2 is empty
can it ignore d2 and still generate all possible combinations from the remaining cells?