Verified answer

I have a macro that does this. Paste the below VBA code into your Visual Basic Editor. To get to your VBE -- goto Tools=>Macro=>Visual Basic Editor. Once in the editor goto Insert=>Module, then paste the code in the blank module. Put the start number of your range in cell B1, the end number in B2, and the number of numbers you need (20) in B3 -- then goto Tools=>Macro=>Macros and run the macro "Random"

Sub Random()

'

' This macro allows the random generation of unique

' numbers. The user specifies how many numbers and

' in what range the numbers should fall.

Dim low 'The starting number of the range -- will not change.

Dim high 'The maximum number of the range -- will not change.

Dim numbers 'How many numbers requested -- this will change.

Dim rowNum 'Which row to insert numbers into -- will change.

Dim colNum 'Which column to insert numbers into -- will not change.

Dim currCell 'Cell to put number into -- will change.

Dim dupCheck 'Cells to check duplicates against -- will change.

Dim rowNumDup 'Row number to create dupCheck -- will change.

Dim numbersOrig 'How many numbers requested -- will not change.

Dim checkCell 'Cell to use to check for duplicates -- will change.

Dim unique 'Number of unique numbers possible -- will not change.

Dim x 'Counter for Status bar

'Define Variables

low = [B1].Value

high = [B2].Value + 1

numbers = [B3].Value

numbersOrig = [B3].Value

rowNum = 2

colNum = 5

rowNumDup = 2

unique = (high - low)

x = 0

'Clear out any numbers that are in column E

Columns("E:E").Select

Selection.ClearContents

'Send message if numbers requested is more than possible unique numbers.

'If numbers requested is more than possible, skip to end of macro.

If unique < numbersOrig Then

MsgBox "Please revise the criteria. There are only " & _

unique & _

" unique numbers between " & _

low & _

" and " & _

(high - 1) & _

"."

GoTo 777

End If

'Define which cell to begin working in.

Set currCell = ActiveSheet.Cells(rowNum, colNum)

Set checkCell = ActiveSheet.Cells(rowNum, colNum)

Set dupCheck = ActiveSheet.Cells(rowNumDup, colNum)

'Label Column E Numbers

ActiveCell.FormulaR1C1 = "Numbers"

ActiveCell.Font.Bold = True

ActiveCell.Font.Underline = xlUnderlineStyleSingle

'Begin generating numbers -- stop when reach requested amount

Do While numbers > 0

x = x + 1

currCell.Value = Int((low - high) * Rnd + high)

'Clear formatting

currCell.Font.Bold = False

currCell.Font.Underline = False

'Check for duplicates beginning with second number generated.

If rowNum > 2 Then

'Reset rowNumDup to 2

rowNumDup = 2

'Continue checking until each preceding number is checked.

Do Until rowNumDup = rowNum

Set dupCheck = ActiveSheet.Cells(rowNumDup, colNum)

'If number matches a previous number, regenerate number by not

'restting the variables.

If dupCheck = currCell Then

numbers = numbers

rowNum = rowNum

x = x - 1

'If a match is found skip to beginning of loop.

GoTo 555

End If

'Set variable to check next cell.

rowNumDup = rowNumDup + 1

Loop

End If

'If number is not a duplicate set variables to generate next number.

numbers = numbers - 1

rowNum = rowNum + 1

Set currCell = ActiveSheet.Cells(rowNum, colNum)

555

'Display Message in Status Bar

Application.StatusBar = "Generating random number " _

& x & " of " & numbersOrig & " and checking for duplicates. There are " _

& numbers & " more random numbers to generate."

Loop

'Sort in numerical order

'Display Message in Status Bar and delay so message is visible.

Application.StatusBar = "Sorting in numerical order."

startTime = Timer

Do While Timer - startTime < 0.75

DoEvents

Loop

Columns("E:E").Select

Selection.sort Key1:=Range("E1"), Order1:=xlAscending, Header:=xlGuess, _

OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _

DataOption1:=xlSortNormal

'Unselect and go to Cell B1

Application.CutCopyMode = False

Range("B1").Select

Application.StatusBar = "Finished."

777

End Sub

in cell A1 type: =rand()*100

this will give you numbers between 0 and 100

hit enter. Format cell to no decimal places(whole numbers)

Now highlight on cell and drag down all the way to cell A20 (copy)

There's your 20 random numbers.

Want 20 more--hit F9