Random Numbers (up to a max value) with no Duplicates - Draw Generator

[Simon2001]

Hello

This old post is quite frankly brilliant and exactly what I needed to create. I have been using a random generator but couldn't figure out how to avoid duplicates.

Bingo Draw No duplicates

Hi All, I found a video on youtube that draws bingo numbers with no repeats so I simply copied the Macro code by hand, the first part works and creates a random set of numbers the second part however is coming back with an error. This part works fine: Sub StartOver() Cells.Clear...

www.mrexcel.com

I've got it working perfect and I'm now trying to figure out how to make the range dynamic so rather than a fixed 90 rows/numbers, I want to be able to enter a value in a cell for maximum number of entries so 16, 32, whatever, etc)

Sub StartOver()
ActiveSheet.UsedRange.Clear
Range("E1") = "Number"
Range("F1") = "Sort"
Range("E2") = 1
Range("E2:E17").DataSeries Step:=1
Range("F2:F17").Formula = "=Rand()"
End Sub

Sub DrawUnique()
Range("F1").CurrentRegion.Sort Key1:=Range("F1"), Header:=xlYes
Range("E100").End(xlUp).Copy Destination:=Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
Range("E100").End(xlUp).Resize(1, 2).Clear
End Sub

Can anyone suggest a way to alter this code to do this?

Thanks

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Simon2001]

Thanks, I've just updated my profile. I've been away for so long!

Running Office 365 on Windows.

 

[Fluff]

Thanks for that, how about

Sub Simon()
   Dim Ary As Variant
   Dim Rws As Long
   
   Rws = Range("B2").Value
   Range("A2:A10000").ClearContents
   Range("A2:A" & Rws + 1).Value = Evaluate("SORTBY(SEQUENCE(" & Rws & "),RANDARRAY(" & Rws & "))")
End Sub

Change B2 to the cell with the number of rows.

 

[Simon2001]

Oh, wow, I don't even need the original code Thanks!

Is there anyway to get it so each time I run the code, it selects the next number rather than do them all in one go. Its to generate competition draws so Id like each number to come out one at a time up to the maximum value in B2

 

[Fluff]

You could use

Sub Simon()
   Dim Ary As Variant
   Dim Rws As Long, i As Long
   
   Rws = Range("B2").Value
   Range("A2:A10000").ClearContents
   Ary = Evaluate("SORTBY(SEQUENCE(" & Rws & "),RANDARRAY(" & Rws & "))")
   
   For i = 1 To Rws
      Range("A1").Offset(i).Value = Ary(i, 1)
      Application.Wait (Now + TimeValue("00:00:05"))
   Next i
End Sub

which will output each number with a 5second delay between them

 

[Simon2001]

PERFECT and thanks VERY much for the help with this. This works great!

 

[Fluff]

You're welcome & thansk for the feedback.

 

[Simon2001]

Quick question, if for some reason I wanted to, could I get the next number to come by pressing enter or space bar rather than a time delay?

 

[Fluff]

Possibly, but it's not something I've ever done, so don't know how.