MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Random Number Generator

Posted by Jason E Todd on May 29, 2001 12:26 PM

Trying to generate random numbers using the discrete option. Do not know how to generate these without repeating numbers. Is there a way to do this? Reply here or to my E-mail address. Thank you.

Posted by Kevin James on May 29, 2001 12:40 PM


Follow this thread and see if it is any help:



Posted by Aladin Akyurek on May 29, 2001 12:43 PM


What follows is due Mark W in response to a similar question at this bord. I can't get at the URL of that thread (probably already archived). Here is the whole exchange, slightly edited:

Posted by Jim L on January 30, 2001 at 10:17:23:

I'd like to generate a series of 36 random
numbers without duplicates. The 36 random
numbers begin at #1 and end at #36.

Posted by Mark W. on January 30, 2001 at 11:33:01:

In Reply to: Random Number without duplicates posted by Jim L on January 30, 2001 at 10:17:23:

1. Enter the formula, =RAND(), into cells A1:A36.
2. Enter the formula, =RANK(A1,$A$1:$A$36), into
cell B1, and copy down.


Posted by Dave Hawley on May 30, 2001 5:36 AM

Hi Jason

Here is some code I wrote for creating random numbers. To use it push Alt+F11 and go to Insert>Module then paste in the code. Push Alt+Q to return to Excel and Save. Now push Alt+F8 and click "RandomNumberGenerator" click Options and assign a shortcut key. In this particular case the code will create 36 random numbers between 1 and 36.

I am very close to completing an add-in that will include a "random number generator" that will allow you to nominate the Lowest number, Highest number and the Number of random numbers. Let me know if you are interested.

Sub RandomNumberGenerator()
'Creates a list of random numbers _
between 1 and 36 in range A1:F6
'Written by OzGrid Business Applications
Dim Rw As Integer, Col As Integer
'Clear the range ready for random numbers
Randomize ' Initialize random-number generator.
For Col = 1 To 6 'Set the Column numbers
For Rw = 1 To 6 'Set the Row numbers
Cells(Rw, Col) = Int((36 * Rnd) + 1)
Do Until WorksheetFunction.CountIf _
(Range("A1:F6"), Cells(Rw, Col)) = 1
Cells(Rw, Col) = Int((36 * Rnd) + 1)

Next Rw
Next Col
End Sub

OzGrid Business Applications

Posted by Mark on September 06, 2001 7:49 PM

Great tip! Simple and effective!