Macro to randomize a range

[david wright]

I have a set of numbers in cells (a1:a20) that I would
like to have resorted in random order each time I open the
workbook. I have searched the newsgroups for excel and I
cannot find one.

Any help would be appreciated
Thanks David

 

[Tom Ogilvy]

Private Sub Workbook_Open()
With ThisWorkbook.Worksheets("Sheet1")
.Columns(2).Insert
.Range("B1:B20").Formula = "=rand()"
.Range("A1:B20").Sort Key1:=.Range("B1"), _
Order1:=xlAscending
.Columns(2).Delete
End With
End Sub

go to the vbe and in the project explorer (treeview) select your
project/workbook. Double click on the thisWorkbook entry in the treeview
for your project. Paste in the above code. If there is an additional
declaration

Private sub Workbook_Open()

End sub

then delete that.

Save the workbook.

 

[david wright]

Tom,
Thank You Very Much

DW

-----Original Message-----
Private Sub Workbook_Open()
With ThisWorkbook.Worksheets("Sheet1")
.Columns(2).Insert
.Range("B1:B20").Formula = "=rand()"
.Range("A1:B20").Sort Key1:=.Range("B1"), _
Order1:=xlAscending
.Columns(2).Delete
End With
End Sub

go to the vbe and in the project explorer (treeview) select your
project/workbook. Double click on the thisWorkbook entry in the treeview
for your project. Paste in the above code. If there is an additional
declaration

Private sub Workbook_Open()

End sub

then delete that.

Save the workbook.
--
Regards,
Tom Ogilvy






.