Pick Names at Random from a List – Excel VBA

0 Flares Filament.io 0 Flares ×

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

6 thoughts on “Pick Names at Random from a List – Excel VBA

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

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

Leave a Reply

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