Excel VBA for Random Selection from List: 3 Easy Methods

Let’s get introduced to our dataset first, which represents some students’ IDs, Names, and Ages.

Method 1 – Excel VBA to Select Only One Random Name from a List

Steps:

• Press ALT + F11 to open the VBA window.

• Click on Insert and select Module.

• Insert the following code in the module:

Sub Select1Random_Name()
    Dim xRow As Long
    xRow = [RandBetween(5,11)]
    Cells(14, 3) = Cells(xRow, 2)
End Sub

• Go back to your sheet.

• Click Developer and Macros to open the Macros dialog box.

• Select the macro name as mentioned in the Codes.
• Press Run.

A random name is picked up from the selection.

By running the Macro again, you will get new random names.

Method 2 – Embed VBA to Select Any Number of Random Names from a List

We have modified the dataset. We specified 4 in Cell E4 to select 4 names at a time and show the output in cells E7:E10.

Steps:

• Open the VBA window and insert a module.
• Insert the following code in the module:

Sub Select_Any_NumberOf_Names()
Dim xNumber As Integer
Dim xNames As Long
Dim xRandom As Integer
Dim Array_for_Names() As String
Dim i As Byte
Dim CellsOut_Number As Long
Dim Ar_I As Byte
Application.ScreenUpdating = False
xNumber = Range("E4").Value
CellsOut_Number = 7
ReDim Array_for_Names(1 To xNumber)
xNames = Application.CountA(Range("A:A")) - 3
j = 1
Do While j <= xNumber
RandomNo:
    xRandom = Application.RandBetween(4, xNames + 1)
    For Ar_I = LBound(Array_for_Names) To UBound(Array_for_Names)
        If Array_for_Names(Ar_I) = Cells(xRandom, 1).Value Then
            GoTo RandomNo
        End If
    Next Ar_I
    Array_for_Names(j) = Cells(xRandom, 1).Value
    j = j + 1
Loop
For Ar_I = LBound(Array_for_Names) To UBound(Array_for_Names)
    Cells(CellsOut_Number, 5) = Array_for_Names(Ar_I)
    CellsOut_Number = CellsOut_Number + 1
Next Ar_I
Application.ScreenUpdating = True
End Sub

• Go back to the sheet.

• Open the Macros dialog box.
• Select the macro name and press Run.

The code has picked 4 random names from the selected list. If you Run the Macro again, you will get a different output each time.

Method 3 – Insert VBA to Select Random Names One by One from a List

We’ll select random names one by one from a list and will show them in a destination range. The destination range is F5:F11.

Steps:

• Open the VBA window and insert a module.
• Enter the following code:

Sub SelectNames_OneByOne()
Dim xSource, xDestination As Range
Set xSource = ActiveSheet.Range("B5:B11")
Set xDestination = ActiveSheet.Range("F5:F11")
ReDim xRandoms(1 To xSource.Rows.Count)
destrow = 0
For k = 1 To xDestination.Rows.Count
If xDestination(k) = "" Then: destrow = k: Exit For
Next k
If destrow = 0 Then: MsgBox "No more space in the output range": Exit Sub
For k = 1 To UBound(xRandoms): xRandoms(k) = Rnd(): Next k
kpick = 0: xtries = 0
Do While kpick = 0 And xtries < UBound(xRandoms)
xtries = xtries + 1
xminrnd = WorksheetFunction.Min(xRandoms)
For k = 1 To UBound(xRandoms)
If xRandoms(k) = xminrnd Then
selected_past = False
For m = 1 To destrow - 1
If xSource(k) = xDestination(m) Then: selected_past = True: xRandoms(k) = 2: Exit For
Next m
If Not selected_past Then: kpick = k
Exit For
End If
Next k
Loop
If kpick = 0 Then: MsgBox "Unique Names Covered": Exit Sub
xDestination(destrow) = xSource(kpick)
End Sub

• Go back to your sheet.

• Open the Macros dialog box.
• Select the macro name and just press Run.

One name is picked up from the list.

• Run the Macro again and you will get another name after the previous one.
• Keep it up to fill the destination range one by one.

Download the Practice Workbook

<< Go Back to Random Selection in Excel | Randomize in Excel | Learn Excel