# all lottery combinations 1 - 49

#### damainman

##### New Member
i need excel to output all lottery combinations. the numbers are from 1-49 from which 6 numbers are drawn. i do realise the number of combinations are 13983816. i want the 6 numbers drawn to be in different cells across 6 different columns. so the 1st 6 numbers are in a1, b1, c1, d1, e1, f1. obviously excel 2003 has 65536 rows and so the formula/vb code should carry on onto a new worksheet or ideally a new workbook. its ideal in a new workbook so not to end up with a single huge 10gig file, but on several worksheets in 1 workbook is fine.

i have been trying to do this myself for a while now with no success. any help would be great. from what i understand; this is a bit of a challenge i know.

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

#### Weaver

##### Well-known Member
Just wondering what it's for?

#### Weaver

##### Well-known Member
Try this:

Code:
``````Sub allLottery()
Dim x As Long
Application.ScreenUpdating = False
x = 1
x = doTheLott("", x, 1)
End Sub
Function doTheLott(ByVal xStr As String, r As Long, a As Integer) As Long
Dim xArr As Variant
xArr = Split(Trim(xStr), " ")
If UBound(xArr) = 5 Then
Cells(r, 1).Resize(1, 6).Value = xArr
r = r + 1
If r > Rows.Count Then
r = 1
End If
Else
For i = a To 49
j = Format(i, "00")
If InStr(xStr, j) = 0 Then
r = doTheLott(xStr & j & " ", r, a + 1)
End If
Next
End If
doTheLott = r
End Function``````
Run allLottery and then go make a coffee. No idea how long it'll take (or even if it actually works...)

#### damainman

##### New Member
its just for personal non work related purposes. i want to be able to then play about with the combinations and query/analyse the list.

it would be great if someone could give me some help.

#### damainman

##### New Member

thanks for that solution....
...i am literrally having to have a tea! lol

i shall let you know what happens!

#### Weaver

##### Well-known Member
Actually, I ran it for a while and there is some repetition. Also, it failed after 17 sheets' worth of numbers! I calculated it'll need at least 200

#### damainman

##### New Member

mines still running! lol

some repetition you say? any solution?

this will atleast hopefully be the closest iv come to solving this problem!

#### damainman

##### New Member
its still running! 600+ sheets created and counting! think its def stuck in a loop or something as it should not require that many sheets!!

Last edited:

#### Weaver

##### Well-known Member
Unless that's the repetition I was talking about - must be worse than I thought. I'll take another look at this, maybe tonight, poss over the weekend. It's probably worth doing on general principles. Whatever I do it needs to be quick!

#### Weaver

##### Well-known Member
I've got a version that saves every time a new sheet is added and 2003 seems to fall over once the file size hits 100mb. Running in 2007 it's still going strong at 173 mb and currently 8 sheets!

Replies
22
Views
431
Replies
3
Views
211
Replies
1
Views
123
Replies
0
Views
147
Replies
4
Views
490

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,711
Messages
5,833,254
Members
430,200
Latest member

### 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.

### Which adblocker are you using?

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

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