Randomizing a list of words in excel

walls07

New Member
I have list of 1,000 words and I would like to mix them up in a random order.
Does anyone know if these is any code I can enter into excel to perform this function?

Thank you
Stephan

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Richard Schollar

MrExcel MVP
Hi Stephen

Give this a try:

Code:
``````Sub RandomizeOrder()
Dim v, temp
Dim i As Long, el As Long
Randomize
v = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
For i = 1 To UBound(v, 1)
el = Int(Rnd * UBound(v, 1)) + 1
temp = v(el, 1)
v(el, 1) = v(i, 1)
v(i, 1) = temp
Next
Range("A1").Resize(UBound(v, 1)) = v
End Sub``````

walls07

New Member
Hi Richard,

Thanks for your quick reply. Where do I need to enter that code? All my words are listed 1-1000 in column A.

Stephan

Richard Schollar

MrExcel MVP
It needs to go into a standard module in your workbook containing the words:

1. With the words visible on in your Excel window, open up the VBE with Alt+F11
2. Insert a standard module with Alt+I then Alt+M
3. Paste the code into the code module which will open on your right
4. Back in Excel, run the macro by Tools>Macro>Macros and select it from the list and hit Run.

Note that I assumed that your words were in column A starting from A1.

walls07

New Member

I understand now. Thanks for your help, much appreciated.

Weaver

Well-known Member
alternatively, if your words are in column A, in column B place the formula

=rand()

copy down then sort the range on column B

BRDParker

New Member
Hi Stephen

Give this a try:

Code:
``````Sub RandomizeOrder()
Dim v, temp
Dim i As Long, el As Long
Randomize
v = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
For i = 1 To UBound(v, 1)
el = Int(Rnd * UBound(v, 1)) + 1
temp = v(el, 1)
v(el, 1) = v(i, 1)
v(i, 1) = temp
Next
Range("A1").Resize(UBound(v, 1)) = v
End Sub``````

Contortionist, you have saved me so much hassle, thank you very much! I have been scouring the forums trying to find this.

northwolves

Well-known Member
An easier one:
Code:
``````Sub RandomizeOrder()
[b1].Resize([a65536].End(3).Row) = "=rand()"
[a:b].Sort [b1]
[b:b] = ""
End Sub``````
Regards
Northwolves

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,484
Messages
5,837,602
Members
430,505
Latest member
DevAlex

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back