The following is a generalized shuffling routine that I have posted in the
past over in the compiled VB newsgroups, but it works fine in the VBA world
of Excel as well. Give it an array of elements and it will put them in
random order and return the randomized elements back in the original array
that was passed to it. It only visits *each* array element *once* so it is
quick. The code takes care of running the Randomize statement one time only
(which is all that is necessary).

Sub RandomizeArray(ArrayIn As Variant)
Dim X As Long
Dim RandomIndex As Long
Dim TempElement As Variant
Static RanBefore As Boolean
If Not RanBefore Then
RanBefore = True
Randomize
End If
If VarType(ArrayIn) = vbArray Then
For X = UBound(ArrayIn) To LBound(ArrayIn) Step -1
RandomIndex = Int((X - LBound(ArrayIn) + 1) * _
Rnd + LBound(ArrayIn))
TempElement = ArrayIn(RandomIndex)
ArrayIn(RandomIndex) = ArrayIn(X)
ArrayIn(X) = TempElement
Next
Else
'The passed argument was not an array
'Put error handler here, such as . . .
Beep
End If
End Sub

After passing your array into the RandomizeArray subroutine, its elements
will be randomly reordered. The passed array may be of any normal type --
integer, string, single, etc. The neat thing is, if you pass an already
randomized array to this routine, those randomly ordered elements will be
randomize -- sort of like shuffling an already shuffled deck of cards.

Rick


"Shatin" wrote in message
...
Can anyone help me with a script that would shuffle the elements of an
array of integers randomly. For example:

MyArray before shuffling: 1, 2, 3, 4, 5
MyArray after shuffling: 3, 5, 4, 2, 1

I want the reordering to be really random.

TIA