# Excel VBA: Random Selection from List (3 Examples)

Get FREE Advanced Excel Exercises with Solutions!

Suppose you have arranged a lottery and so you want to pick winners’ names randomly. You can do this type of task easily using Excel VBA. In this article, I’ll show 3 useful VBA macros for random selection from a list in Excel with easy steps and vivid illustrations.

## Excel VBA for Random Selection from List: 3 Examples

Let’s get introduced to our dataset first that represents some students’ IDs, Names, and Ages. ### 1. Excel VBA to Select Only One Random Name from a List

First, I’ll use a simple VBA macro to select only one item at a time from a list and will show the output in Cell C14.

Steps:

• Press ALT + F11 to open the VBA window. • Then click as follows: Insert ➤ Module. • Later, type the following codes in it-
``````Sub Select1Random_Name()
Dim xRow As Long
xRow = [RandBetween(5,11)]
Cells(14, 3) = Cells(xRow, 2)
End Sub``````
• After that go back to your sheet. Code Breakdown

• First, I created a Sub procedure Select1Random_Name.
• Then declared a variable xRow as Long.
• Next, used the function RandBetween to select a random item from the row range 5 to 11.
• Finally, Cells(14, 3) = Cells(xRow, 2) will show the output in Cell C14.

• Then click as follows: Developer ➤ Macros to open the Macros dialog box. • Next, select the macro name as mentioned in the Codes.
• Finally, just press Run. Soon after, you will see that a random name is picked up from the selection.

By running the Macro again and again you will get new random names. Read More: Random Selection from List with No Duplicates in Excel

### 2. Embed VBA to Select Any Number of Random Names from a List

Here, We’ll select a set of random names at a time according to our specified number. For this example, I have modified the dataset. I specified 4 in Cell E4 to select 4 names at a time and will show the output in cells E7:E10. Steps:

``````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``````
• Later, go back to the sheet. Code Breakdown

• Here, I created a Sub procedure Select_Any_NumberOf_Names.
• Then declared some variables- xNumber As Integer, xNames As Long, xRandom As Integer, Array_for_Names() As String, i As Byte, CellsOut_Number As Long, Ar_I As Byte.
• Next, used Range(“E4”).Value to pick the selection number from Cell E4.
• CellsOut_Number = 7 is the first-row number to place the output.
• ReDim Array_for_Names(1 To xNumber) will resize the array for the selected names.
• Then used CountA(Range(“A:A”)) – 3 to determine names in the list.
• Next, I used a Do While loop to go through the name_list until I get 4 values, these 4 values are selected by using the VBA RandBetween function.
• Then, I used a For Loop to extract the values from the list where I used an IF statement to check if the cells contain values or not.
• Also used another For Loop to place those extracted random selections in the selected cell. Now see that that code has picked 4 random names from the selected list. If you Run the Macro again then you will get different output each time. ### 3. Insert VBA to Select Random Names One by One from a List

In our last example, we’ll select random names one by one from a list and will show them in a destination range. Here, my destination range is F5:F11. I’ll fill the destination one by one. Steps:

``````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``````
• Then go back to your sheet. Code Breakdown

• First, I created Sub procedure SelectNames_OneByOne.
• Then I declared two variablesxSource, xDestination As Range.
• Next, used Range(“B5:B11”) to set the range B5:B11 as source from the active sheet.
• And used Range(“F5:F11”) to set the destination range in the active sheet.
• Then, used an IF statement to check the destination range empty or not within the For Loop.
• Again, used the IF statement within the Nested For Loop to pick the Random selection one by one through the Do While loop. 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. ## Practice Section

You will get a practice sheet in the Excel file given above to practice the explained ways. ## Conclusion

I hope the procedures described above will be good enough to use Excel VBA for random selection from a list. Feel free to ask any question in the comment section and please give me feedback.

## Related Articles Md. Sourov Hossain Mithun

Hello! I am Md. Sourov Hossain Mithun. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is an amazing software to learn or work. Here, I will post Excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

1. Reply Hi. Good Day! This is very helpful to me. I just have a question on Random Selection from List. I hope you can help me on this one. I am currently working on ‘Embed VBA to Select Any Number of Random Names from a List’ but what if names have multiple data and I want to random select based on the quantity I put. Is there any function for this scenario? Thanks.

• Reply MD Tanvir Rahman Aug 21, 2023 at 1:40 PM

Dear Jae, You have my heartfelt gratitude. I found your queries quite distinctive and innovative. Yes, you can find out Names based on multiple data. Although there is no single functions in VBA that can extract result based on data, you must apply multiple VBA functions such as SPLIT, COUNTA, OFFSET, IF to get the job done.

Step 1: Write the VBA code in the module and hit the Run icon. VBA Code

``````
Sub Find_Names_Based_on_Multiple_Data()
Dim rng As Range
Dim words() As String
Dim ws As Worksheet
Dim text As String
Dim i As Long
Dim j As Long
Dim countResult As Long
Set rng = Range("A1:A8")
Set ws = ThisWorkbook.ActiveSheet

'Clearing cells of output
Range("B2:D8").Delete
Range("A13:A17").Delete

For i = 2 To rng.Rows.Count     'Executing FOR loop to consider each row
text = Cells(i, 1).Value    'Picking up cell values from A2:A8 range
words = Split(text, " ")    'Applying Array to split based on space
For j = 2 To UBound(words) + 2  'Applying FOR loop for column counting
ws.Cells(i, j).Value = words(j - 2) 'split data based on column A
Next j
'Counting column containing text in each row
countResult = Application.WorksheetFunction.CountA(Range("B" & i & ":E" & i))
If countResult = ws.Range("B10").Value Then     'matching column no to Value of B10
If ws.Cells(13, 1).Value = "" Then  'If there is no  value then write output in A13
ws.Cells(13, 1).Value = Cells(i, 1).EntireRow.Value
Else    'If multiple value appears then write below the existing
ws.Cells(ws.Rows.Count, 1).End(xlUp).Offset(1, 0).Value = Cells(i, 1).EntireRow.Value
End If
End If
Next i
End Sub
``````

Step 2: Obtain output as follows. Thanks a ton. Have a good day.
Regards,
MD Tanvir Rahman
Excel and VBA Content Developer
ExcelDemy, Softeko Advanced Excel Exercises with Solutions PDF  