Shuffling a Data List

[Don Lloyd]

Is anyone aware of a simple VBA routine to shuffle the contents of a list in
random fashion.

Don

 

[Tushar Mehta]

Random Selection
http://www.tushar-mehta.com/excel/newsgroups/rand_selection/index.html

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Tom Ogilvy]

here's another: (assuming the list is in a column - as written, starting in
A1)

Sub Shuffle()
'
' Algorithm from:
' The Art of Computer Programming: _
' SemiNumerical Algorithms Vol 2, 2nd Ed.
' Donald Knuth
' p. 139 [Algorithm P]
'
'
Dim list As Variant
Dim rng As Range
Dim t As Long, j As Long, k As Long
t = 100
Set rng = Range(Cells(1, 1), Cells(1, 1).End(xlDown))
list = rng.Value
t = UBound(list, 1)
j = t
Randomize
For i = 1 To t
k = Rnd() * j + 1
lngTemp = list(j, 1)
list(j, 1) = list(k, 1)
list(k, 1) = lngTemp
j = j - 1
Next
rng.Value = list
End Sub

 

[Don Lloyd]

Than you Tom,

Just the job. I like the use of an array for doing the spadework.

Don

here's another: (assuming the list is in a column - as written, starting
in
A1)

Sub Shuffle()
'
' Algorithm from:
' The Art of Computer Programming: _
' SemiNumerical Algorithms Vol 2, 2nd Ed.
' Donald Knuth
' p. 139 [Algorithm P]
'
'
Dim list As Variant
Dim rng As Range
Dim t As Long, j As Long, k As Long
t = 100
Set rng = Range(Cells(1, 1), Cells(1, 1).End(xlDown))
list = rng.Value
t = UBound(list, 1)
j = t
Randomize
For i = 1 To t
k = Rnd() * j + 1
lngTemp = list(j, 1)
list(j, 1) = list(k, 1)
list(k, 1) = lngTemp
j = j - 1
Next
rng.Value = list
End Sub

--
Regards,
Tom Ogilvy

Is anyone aware of a simple VBA routine to shuffle the contents of a list in
random fashion.

Don