Sort a range randomly

H

hotherps

I have a range of data
A11:DU298

I want to sort the whole range randomly Column F is null. The formul
=Rand() can be put in each cell in F. But when I did that and then ra
some different code. The function in F just kept changing randomly.

I only want to sort the range by random once and then run my unrelate
code.

Thank
 
B

BrianB

after copying the rand formula down convert to numbers :-
select/edit/paste special/ values

Then sort on that colum
 
H

hotherps

I need the sort to happen everytime I run the code, not just one time
Because it will continue to sort in the same order if I paste values t
that column. If I'm understanding you correctly
 
T

Tom Ogilvy

The rand() function will change each time the sheet is recalculated (which
occurs after the sort). If you don't need the numbers in column F, then
just disregard them (or clear them after the sort). If you want feedback
that the sort was actually in some order, then you can past special values
before the sort as the others have suggested.
 
H

hotherps

OK I understand what all of you mean now. I wasn't getting it at first.

But let me ask you this

Is there a way I could simulate that the F9 (autocalc) function before
the sort runs?

Then I would not have to clear it or add it

Thanks to all
 
T

Tom Ogilvy

If the code adds the formula each time, it should make no difference. If
you added the formula manually, then after each sort, it is already ready to
do the next one. It sounds like it is doing exactly what you need? What is
the real problem? Perhaps you haven't explained it correctly.
 
H

hotherps

I know it was alittle confusing, but I think it is OK now.

The real problem was, once my unrelated code starting running after the
random sort. All of the cells in that (F) column were randomly changing
as the code was running and it was really slowing it down.

Thanks again
 
T

Tom Ogilvy

You set calculation to manual in your code

Application.Calculation = xlManual

and perhaps later set it back to automatic

Application.Calculation = xlAutomatic

if at some point you want to force a calculation


Application.Calculate
 
Top