Shuffle and randomise csv file rows

Question

I am having trouble with a VBA excel macro that I need to complete.

I have n columns and rows (these amounts will always be different depending on the .csv file I want to process),
I would like to be able to randomize the rows using a VBA macros

I know that =RAND() would solve this, but this is what I would like to be able to automate and call when needed

Example table:

1   798   tea     ...
2   889   coffee  ...
3   990   mocca   ...
4   282   latte   ...
...

I would like the rows to be shuffled about like this: Example table:

1   282   latte   ...
2   798   tea     ...
3   889   coffee  ...
4   990   mocca   ...
...

This may be a trivial question to most, but I am new to VBA.

Here is what I have tried, but I am limited to explicitly typing the range each time, which I do not want to do.

Sub Shuffle()
Dim list As Variant
Dim rng As Range
Dim t As Long, j As Long, k As Long
t = 100
'The line below seems to be the problem, I don't know how to reference the range values?
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

Any help would be greatly appreciated.

You are looking to randomise rather than ascending sort by number (which your example shows?) — brettdj, Jul 18 '14 at 09:00

score 1 · Answer 1 · answered Jul 18 '14 at 10:33

1

If you want to learn how to do this in standard VBA we can certainly help. A quicker solution though - that could be run external from Excel - is to use PowerShell

To randomly sort a csv file C:\temp\test.csv and write to a new file C:\temp\test2.csv you could

vba

 Sub Better()
 X = Shell("powershell.exe  $users = gc C:\temp\test.csv ;Get-Random  -InputObject $users -Count $users.Count  | out-file C:\temp\test2.csv -Encoding ascii", 1)
 End Sub

powershell

$users = gc C:\temp\test.csv
Get-Random  -InputObject $users -Count $users.Count  | out-file C:\temp\test2.csv -Encoding ascii

answered Jul 18 '14 at 10:33

brettdj

54,857
16
114
177

I will not be using Powershell to do this, can anyone help with the vba code required to shuffle & randomise rows in excel please? – Jimmy8t8 Jul 19 '14 at 05:50
I will take another look at this later today – brettdj Jul 19 '14 at 06:16

score 1 · Accepted Answer · edited May 23 '17 at 12:27

You can set the true used range using Return a range from A1 to the true last used cell
add a working column with =RAND
then do a Sort and remove the working column

Which I think is cleaner than swapping entire rows in the array

code

Sub Shuffle()

Dim rng1 As Range
Dim rng2 As Range

Dim rng3 As Range

Set rng1 = Cells.Find("*", [a1], xlValues, , xlByRows, xlPrevious)
Set rng2 = Cells.Find("*", [a1], xlValues, , xlByColumns, xlPrevious)
Set rng3 = Range([a1], Cells(rng1.Row, rng2.Column + 1))

With rng3
.Columns(rng3.Columns.Count).FormulaR1C1 = "=RAND()"
'not strictly needed but shows the sort order before a recalc
.Columns(rng3.Columns.Count).Value = .Columns(rng3.Columns.Count).Value
.Sort Cells(1, rng3.Columns.Count), xlAscending
.Columns(rng3.Columns.Count).ClearContents
End With

End Sub

Shuffle and randomise csv file rows

2 Answers2