• Skip to main content
  • Skip to primary sidebar
  • Skip to footer

Microsoft Excel Tips and Tricks - Computergaga

  • Home
  • Excel Online Courses
  • Excel Functions List
  • Free Downloads
  • Blog
  • Tutorials
  • About
Home / VBA / Pick Names at Random from a List – Excel VBA

Pick Names at Random from a List – Excel VBA

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

Watch the Video

More Awesome Excel VBA Tutorials

  • Macro to Export All Worksheets as PDF
  • Great Tip to Add a Hyperlink to a Userform
  • Format Specific Words in a Cell using Excel VBA
  • Create a Hangman Game in Excel

Related Posts:

  • Compare dates in Excel feature
    How to Compare Dates in Excel
  • SWITCH function feature
    Excel SWITCH Function - Nested IF Function Alternative
  • N Functions in Excel thumbnail
    N Function in Excel
  • Live currency exchange rate in Excel thumbnail
    LIVE Currency Exchange Rate in Excel

Reader Interactions

Comments

  1. Bruce says

    24 April 2016 at 9:58 pm

    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?

    Reply
    • computergaga says

      27 April 2016 at 6:45 am

      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

      Reply
  2. Rashid says

    24 June 2016 at 5:30 pm

    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

    Reply
    • carl says

      28 July 2016 at 5:52 pm

      I tried this and i’m getting a bug in the code. any chance you can email the template?

      Reply
      • computergaga says

        28 July 2016 at 9:10 pm

        Hi Carl,
        Template can be found at http://www.computergaga.com/excel/files/pick-names-at-random.xlsm
        Alan

        Reply
  3. aditya says

    8 September 2016 at 5:52 pm

    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.

    Reply
  4. Roy says

    15 October 2016 at 11:59 am

    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.

    Reply
  5. Cara says

    8 December 2016 at 6:09 pm

    I am getting an error code “Type Mismatch” on the line “CellsOut = 6”. Any idea why?

    Reply
    • computergaga says

      12 December 2016 at 2:22 pm

      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.

      Reply
  6. Lincoln T says

    28 December 2016 at 7:20 pm

    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.

    Reply
    • computergaga says

      28 December 2016 at 9:31 pm

      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.

      Reply
      • Lincoln T says

        29 December 2016 at 9:50 pm

        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.

        Reply
        • computergaga says

          1 January 2017 at 2:45 pm

          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

          Reply
          • Lincoln T says

            3 January 2017 at 5:48 pm

            Thanks so much for your help. That solved my problem.

  7. Cammi says

    19 January 2017 at 10:15 am

    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

    Reply
    • Cammi says

      19 January 2017 at 11:03 am

      To supplement the runtime error is “6 Overflow”. Thanks.

      Reply
      • computergaga says

        22 January 2017 at 8:29 pm

        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.

        Reply
  8. Martin says

    29 March 2017 at 11:59 am

    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

    Reply
    • computergaga says

      30 March 2017 at 8:07 am

      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

      Reply
  9. Morgan says

    3 October 2017 at 11:26 pm

    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?

    Reply
    • computergaga says

      6 October 2017 at 7:05 am

      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

      Reply
  10. rodney says

    19 October 2017 at 3:53 am

    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?

    Reply
    • computergaga says

      19 October 2017 at 10:52 pm

      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

      Reply
  11. Kady says

    16 January 2018 at 12:44 am

    Is there anyway, not to select the same name for a set period of time.

    Reply
    • computergaga says

      16 January 2018 at 5:52 am

      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.

      Reply
  12. Scott Johnson says

    18 February 2018 at 3:42 am

    Can the random list be displayed in a VBA message box?

    Reply
    • computergaga says

      18 February 2018 at 6:52 am

      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

      Reply
  13. Charles Harris says

    10 July 2020 at 3:16 am

    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

    Reply
  14. Maria says

    18 December 2020 at 5:17 pm

    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?

    Reply
    • Alan Murray says

      23 December 2020 at 11:08 am

      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.

      Reply
  15. Samantha says

    18 December 2020 at 9:06 pm

    How can I delete the row that the name has been chosen from? Is this possible?

    Thanks!

    Reply
    • Alan Murray says

      23 December 2020 at 11:05 am

      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.

      Reply
  16. Jeff says

    11 January 2021 at 8:54 pm

    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

    Reply
    • Alan Murray says

      11 January 2021 at 8:56 pm

      Sure. Here is the link – http://www.computergaga.com/_excel/files/random-names.xlsm

      Reply
  17. Jamshaid Sultan Durrani says

    20 April 2021 at 9:56 pm

    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

    Reply
  18. Nicole Smith says

    19 June 2021 at 12:42 am

    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…

    Reply
    • Alan Murray says

      26 July 2021 at 9:14 pm

      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

      Reply
  19. GertTrompet says

    27 January 2022 at 4:07 pm

    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

    Reply
  20. Rob says

    28 February 2022 at 11:32 pm

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

    Reply
  21. Hadi says

    4 April 2023 at 9:31 pm

    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

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar

Recent Posts

  • Excel SWITCH Function – Nested IF Function Alternative
  • Sum Formulas Only in Excel
  • How to Calculate Age in Excel
  • Custom Sort in Excel
  • Case Sensitive Lookup with XLOOKUP in Excel
Advanced Excel Success book

Footer

Contact

[email protected]

LinkedIn Twitter YouTube

Useful Links

  • Home
  • Excel Online Courses
  • Tutorials
  • Sitemap
  • Privacy Policy
  • Jobs by Jooble

Course Topics

  • Blog
  • Charts
  • Courses
  • Dashboards
  • Downloads
  • Excel Tips
  • Formulas
  • Mail Merge
  • Office
  • PivotTables
  • Power BI
  • Power Pivot
  • Power Query
  • VBA

Join Our Mailing List

© Copyright 2009 Computergaga · All Rights Reserved ·