Paul,

How about 4 nice blocks of 50,000 rows each? The code will take a little
while to run....

Sub Sets2()
Application.ScreenUpdating = False
Dim my(1 To 49)
Dim j As Long

For j = 0 To 199999
' Reinitialize Array Before Selecting.
For I = 1 To 49
my(I) = I
Next I

For k = 1 To 6
Randomize
NewNumber:
Number = Int(49 * Rnd) + 1
If my(Number) = "" Then
GoTo NewNumber
Else
Cells(j Mod 50000 + 1, k + 9 * (j \ 50000)) = my(Number)
my(Number) = ""
End If
Next k
Next j
Application.ScreenUpdating = True
End Sub

--
HTH,
Bernie
MS Excel MVP


"Paul Black" wrote in message
.. .
Hi Everyone,

Tom Ogilvy Posted the Following Code in 2001 for Generating Random
Numbers :-

Sub Sets()
Application.ScreenUpdating = False
Dim my(1 To 49)

For j = 1 To 10
' Reinitialize Array Before Selecting.
For I = 1 To 49
my(I) = I
Next I

For k = 1 To 6
Randomize
NewNumber:
Number = Int(49 * Rnd) + 1
If my(Number) = "" Then
GoTo NewNumber
Else
Cells(j, k) = my(Number)
my(Number) = ""
End If
Next k
Next j
Application.ScreenUpdating = True
End Sub

This Works Excellent and Does NOT give a Repeated Number ( 1 to 49 )
in Any Set of Six Numbers.
I would like to be able to Produce a Random Number List of 200,000
Sets of Six Numbers.
How can the Above Code be Modified so that when it Reaches 65,000 Sets
of Six Numbers it Moves Nine Columns to the Right and goes Back to Row
One. I know it needs something like :-

ActiveCell.Offset(-65000, 9).Select

I do NOT know where this should be Entered into the Above Code OR the
Extra Coding that is Needed in Order for this to Work.
Any Help will be Appreciated.

All the Best
Paul