# 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

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### 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

Replies
10
Views
187
Replies
9
Views
159
Replies
4
Views
131
Replies
4
Views
275
Replies
2
Views
85

1,129,587
Messages
5,637,268
Members
416,963
Latest member
samfuge

### 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.

### Which adblocker are you using?

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