In this blog post, we will explore how to pick names at random from a list using Excel VBA.
Suppose we were picking the names for a draw or competition and needed to generate a list of maybe 3, 5 or any number of names from a list.
These names must be selected at random and be unique. You cannot select the same name more than once.
The macro code shown below can be copied and pasted into a VBA module and adapted for your use. Comments have been used to identify the key and more complex parts of the macro.
This macro uses an array variable to store the names being randomly selected from the list. Every time a name is selected, it is checked against this array to ensure that it has not already been selected. If it has, then another name is randomly selected. This macro demonstrates some key VBA techniques including a Do While and a For Next loop. It also uses variables including a string array and an If statement.
Want to improve your Excel VBA skills. Take our online course to master Excel VBA quickly.
Macro to Pick Names at Random from a List
Sub PickNamesAtRandom()
Dim HowMany As Integer
Dim NoOfNames As Long
Dim RandomNumber As Integer
Dim Names() As String 'Array to store randomly selected names
Dim i As Byte
Dim CellsOut As Long 'Variable to be used when entering names onto worksheet
Dim ArI As Byte 'Variable to increment through array indexes
Application.ScreenUpdating = False
HowMany = Range("D3").Value
CellsOut = 6
ReDim Names(1 To HowMany) 'Set the array size to how many names required
NoOfNames = Application.CountA(Range("A:A")) - 1 ' Find how many names in the list
i = 1
Do While i <= HowMany
RandomNo:
RandomNumber = Application.RandBetween(2, NoOfNames + 1)
'Check to see if the name has already been picked
For ArI = LBound(Names) To UBound(Names)
If Names(ArI) = Cells(RandomNumber, 1).Value Then
GoTo RandomNo
End If
Next ArI
Names(i) = Cells(RandomNumber, 1).Value ' Assign random name to the array
i = i + 1
Loop
'Loop through the array and enter names onto the worksheet
For ArI = LBound(Names) To UBound(Names)
Cells(CellsOut, 4) = Names(ArI)
CellsOut = CellsOut + 1
Next ArI
Application.ScreenUpdating = True
End Sub
Hey, thanks soo much for this. quick question though.
How could i make this loop like 25 times so that it looks like it is scrolling through the names 25 times before the final list of x amount of names is displayed?
You could use a For Next loop. These are great to loop a pre-determined number of times like your 25. Something like;
Dim i as long
For i = 1 to 25
‘in this space is the movement between the cells with names in
Next
Nice Job done sir.
i need this template excel sheet “Pick Names at Random from a List – Excel VBA” .
Please send me by email given below
I tried this and i’m getting a bug in the code. any chance you can email the template?
Hi Carl,
Template can be found at http://www.computergaga.com/excel/files/pick-names-at-random.xlsm
Alan
hello i want to generate a formula to create a random list of people and that list should randomly arranged in a format i have.
how can i do that.
i saw yours video and tried them but i always get a #valve!.
how should i work on it.
Hi there, thanks a lot for this – fantastic. Could you also tell me how to make this adjustment:
I would like to choose randomly from a range as in the video, but I want that range to be dependant on a string value in the previous column. So rather than simply choosing everything in column A – (Range(“A:A”), I want to say “randomly pick 5 entries from column B where column A = “Blue”. This would allow me to randomly pick any blue card, ignoring the other colours. Ideally I would like to be able to input any string into that and have it change the range. So maybe “Yellow”, “pink”, etc.
I am getting an error code “Type Mismatch” on the line “CellsOut = 6”. Any idea why?
This error indicates an error in the formatting of the variable or assignment. Double check the CellsOut variable was declared and as a number format such as long or integer.
Couple of questions. This is excellent code by the way and is very helpful.
#1. I can’t seem to change the column from A:A to something else. Is this possible?
#2. Is there a way to start the data on a row different than row 1?
Thanks again for your help.
Absolutely for both questions Lincoln. You can change the A:A in the code to move the column. And the change the row number edit the i = 1 line.
I made the changes you suggested but the code is still looking at Column A and not excluding the empty rows and headings. I downloaded the template and updated the macro from there. The only place I found the reference of a column was on the line that starts with NoOf Names. Any ideas? I am trying to use Column C instead of A for the Data and I have my headings on row 5 with data starting on 6 thru 20.
I apologize if I am missing the solution. I am a beginner at this and really do appreciate your help.
Hi Lincoln,
If your list of names are in C6:C20 and everything else is identical then the following code should work. I counted range C6:C30 so exaggerated your names incase you needed a few more rows.
Sub PickNamesAtRandom()
Dim HowMany As Integer
Dim NoOfNames As Long
Dim RandomNumber As Integer
Dim Names() As String ‘Array to store randomly selected names
Dim i As Byte
Dim CellsOut As Long ‘Variable to be used when entering names onto worksheet
Dim ArI As Byte ‘Variable to increment through array indexes
Application.ScreenUpdating = False
HowMany = Range(“D3”).Value
CellsOut = 6
ReDim Names(1 To HowMany) ‘Set the array size to how many names required
NoOfNames = Application.CountA(Range(“C6:C30”)) – 1 ‘ Find how many names in the list
i = 1
Do While i <= HowMany RandomNo: RandomNumber = Application.RandBetween(6, NoOfNames + 6) 'Check to see if the name has already been picked For ArI = LBound(Names) To UBound(Names) If Names(ArI) = Cells(RandomNumber, 3).Value Then GoTo RandomNo End If Next ArI Names(i) = Cells(RandomNumber, 3).Value ' Assign random name to the array i = i + 1 Loop 'Loop through the array and enter names onto the worksheet For ArI = LBound(Names) To UBound(Names) Cells(CellsOut, 4) = Names(ArI) CellsOut = CellsOut + 1 Next ArI Application.ScreenUpdating = True End Sub
Thanks so much for your help. That solved my problem.
Hi Alan,
When I try to run 600 names, the macro stop and show run time error. How do I fix this problem? thanks.
Cammi
To supplement the runtime error is “6 Overflow”. Thanks.
The i and array variables are set to the Byte data type which only handles values up to 256. Change it to the Integer or Long data type.
Thanks for the tutorial Alan!
I would like to add a drumroll to the script – is there any way you can assist with this?
What I’m looking for is basically what you’ve done, but instead of generating names instantly I want to play the drumroll and wait a couple of seconds and show the generated names.
Best regards,
Martin
Hi Martin,
Not something I have done before, though I believe playing a sound using VBA code is possible. The delay can be done for sure.
I can’t help right now I’m afraid.
Alan
How do I make this code work if I want to have a command button that I push to make it spit out a random list. the only way I can get it work now is to change the number of people I want to choose and then to click the run button in the VBA window. I want to run the code from a button on the main spreadsheet. Also, if I wanted to have the names to choose from on a different sheet than where the list is generated, how would I modify this code?
Hi Morgan,
Please check out this file with the code included – Random Names Excel VBAThe command button is on sheet 2 with the names on sheet 1 as you asked.
Alan
How can we fix the issue of Names in the cell mismatch the number in D3? Like if there are only 8 names in the list and i accidentally select 9, the template hangs. Is there a way to give a pop up message to alert the user and not run the code if the names go beyond the number range?
Sure. Immediately before the Do While loop begins you could enter…
If HowMany >= NoofNames Then
MsgBox “You have selected too many names.”
Exit Sub
End If
Is there anyway, not to select the same name for a set period of time.
I guess it could be done. Depends on the time period you were thinking also. The names would need to be removed by the code and then re-introduced after this time period.
Can the random list be displayed in a VBA message box?
Absolutely. The block of code that prints the names to the worksheets could be changed to the below.
Declare a variable named PrintNames and then use this to store a list of names and use in the message box
‘Loop through the array and enter names onto the worksheet
For ArI = LBound(Names) To UBound(Names)
PrintNames = PrintNames & Names(ArI) & vbCrLf
Next ArI
MsgBox PrintNames
Hi
Code works great. But – I can move the…… HowMany = Range(“D3”).Value to any other cell and still works fine, and I can move the column for selected names ok.
However if I want to move the original list: …..NoOfNames = Application.CountA(Range(“A:A”)) – 1
to another location say (“K:K”)) – 1 it will not work.
What do I need to change also with the change to get it to work.Similar to Lincoln message above.
Thanks
Charles
How do I choose 2 name columns to display as the winner. I have an excel spreadsheet that has last name in column A and first name in column B?
You can use a multi dimension array to include two columns. Or just ignore the array and output straight to the sheet if it is only one name. Generate the random number and then just output both the first and last name. Something like – Cells(RandomNumber, 1).Value & ” ” Cells(RandomNumber, 2).Value if you want them in the same cell.
How can I delete the row that the name has been chosen from? Is this possible?
Thanks!
Sure. When you assign the name to the array and increment the array index. At this area you could delete it. A simple line such as Rows(RandomNumber).Delete will do the job. Bear in mind this deletes the entire row. There are other approaches.
And if you delete a name you will need to also adjust the NoofNames variable by removing one.
Hi there:
I am new to VBA and trying to get this to operate in excel.
The links in older comments for a template do not seem to be working, can you link to a template of this VBA macro in Excel that I can access?
Thank you
Sure. Here is the link – http://www.computergaga.com/_excel/files/random-names.xlsm
can you please guide how to put array values in multiple columns like first four values in first column , than 5 values in second column, and than may be 2 in second column….. and so on. i tried do while loop and for loop but the results are not satisfactory ————————-
Sub PickNamesAtRandom()
Dim HowMany As Long
Dim NoOfNames As Long
Dim RandomColumn As Integer
Dim RandomRow As Integer
Dim Names() As String ‘Array to store randomly selected names
Dim i As Byte
Dim CellsOutRow As Integer
Dim CellsOutColumn As Integer ‘Variable to be used when entering names onto worksheet
Dim ArI As Byte ‘Variable to increment through array indexes
Application.ScreenUpdating = False
HowMany = WorksheetFunction.Sum(Sheets(“test”).Range(“A2:E2”))
CellsOutRow = 3
CellsOutColumn = 1
ReDim Names(1 To HowMany) ‘Set the array size to how many names required
NoOfNames = Application.CountA(Sheets(“sheet1”).Range(“D4:L45”)) ‘ Find how many names in the list
i = 1
Do While i <= HowMany
RandomNo:
RandomRow = Application.RandBetween(1, 45)
RandomColumn = Application.RandBetween(1, 15)
'Check to see if the name has already been picked
For ArI = LBound(Names) To UBound(Names)
If Names(ArI) = Sheets("sheet1").Cells(RandomRow, RandomColumn).Value Then
GoTo RandomNo
End If
Next ArI
Names(i) = Sheets("sheet1").Cells(RandomRow, RandomColumn).Value ' Assign random name to the array
i = i + 1
Loop
Dim RequiredRows As Integer
RequiredRow = 2
'Loop through the array and enter names onto the worksheet
For ArI = LBound(Names) To UBound(Names)
Do
Cells(CellsOutRow, CellsOutColumn) = Names(ArI)
CellsOutRow = CellsOutRow + 1
Loop While CellsOutRow < Cells(RequiredRow, CellsOutColumn).Value
CellsOutColumn = CellsOutColumn + 1
Next ArI
Application.ScreenUpdating = True
End Sub
How would I edit this code to have the names from my list populate in multiple columns as opposed one? Like have 4 names populate in column C, 4 names in column D, etc…
I won’t be able to help with specific requests such as this. Excel Rescue can solve this for you – http://bit.ly/2Ms1d2h
I also have an Excel VBA course, if you want to master VBA yourself to know how to handle these scenarios – http://bit.ly/37XSKfZ
Great code!
When I run the macro today, it actually takes the values from the HowMany variable and sticks those in the output column. I would like it to perform as follows:
– the list of names is on different sheet (product list for instance)
– the total number of records is the number of records on the currently active sheet where I want to run the macro
Nice code, does what I have been looking for, mostly.
The code only pulls from one column of data.
Is there a way to adjust it to pull a row? And have the rows copied to the selected location in the order it was copied.
For example: A2,B2,C2 copied to D6,E6,F6 (and of course related to “how many” so if I typed “How Many” 7 it will pull 7 random rows).
Hello Rob,
Thanks a lot for the lesson.
Question: How can I do this macro in multiple cells contain “How many?”
What I mean is, in your lesson you have “D3” to get the value. so if I have many cells have different values, like if I need to pick 3 names to work in Sunday, and pick another 5 names to work in Monday … etc.
I hope you got the idea.
Thanks