[Paul B.]

I would like to generate a row of 5000 - unique random numbers and not have
them change once generated.

Any ideas would be appreciated.

Cheers

[joeu2004]

On Oct 18, 12:20 pm, Paul B. wrote:
I would like to generate a row of 5000 - unique random numbers
and not have them change once generated.

Irritating, isn't it?

One way: Create a UDF that calls the VBA function Rnd().

Another way: After generating the column using =RAND(), copy the
column, then use Paste Special -- Values to replace the column.

[joeu2004]

[reposting]

On Oct 18, 12:20 pm, Paul B. wrote:
I would like to generate a row of 5000 - unique random
numbers and not have them change once generated.

Irritating, isn't it?

One way: Create a UDF that calls the VBA function Rnd(),

Another way: After generating the column using =RAND(), copy the
column, then use Paste Special -- Values to replace the column with
the generated values.

[[email protected]]

[reposting]

On Oct 18, 12:20 pm, Paul B. wrote:
I would like to generate a row of 5000 - unique random
numbers and not have them change once generated.

Irritating, isn't it?

One way: Create a UDF that calls the VBA function Rnd(),

Another way: After generating the column using =RAND(), copy the column,
then use Paste Special -- Values to replace the column with the generated
values.

[[email protected]]

Errata....

I wrote:
One way: Create a UDF that calls the VBA function Rnd().

But Excel might still recalculate those calls when it feels like it, namely
(I think) when it deletes a worksheet and when it saves or loads (I don't
recall which) the workbook.

Of course, you could always turn off automatic recalculation. But the calls
will be recalculated when you eventually manually recalculate the worksheet.

Another way: After generating the column using =RAND(), copy the
column, then use Paste Special -- Values to replace the column.

This is the only way I know of to ensure that the random numbers are never
replaced. If you want the option to regenerate the column of random numbers,
Paste Special Values into another column and be sure that your formulas refer
to the "value only" column. You can even create a macro to do the Paste
Special Values, and have a button to run the macro. The column with =RAND()
can be hidden.

Gotta run! HTH.

[jester23423]

Originally Posted by Paul B.

I would like to generate a row of 5000 - unique random numbers and not have
them change once generated.

Any ideas would be appreciated.

Cheers

Copy the randomly generated numbers and then paste values over the same space. Past values is under the paste sub-menu when you right click a cell.