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.

Excel VBA to Select Only One Random Name from a List

  • Click on Insert and select Module.

Excel VBA to Select Only One Random Name from a List

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

Excel VBA to Select Only One Random Name from a List

Code Breakdown

  • We created a Sub procedure Select1Random_Name.
  • We used the function RandBetween to select a random item from the row range 5 to 11.
  • Cells(14, 3) = Cells(xRow, 2) will show the output in Cell C14.

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

Excel VBA to Select Only One Random Name from a List

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

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

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.

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

Code Breakdown

  • We created a Sub procedure Select_Any_NumberOf_Names.
  • We 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.
  • CountA(Range(“A:A”)) – 3 determines names in the list.
  • A Do While loop goes through the name_list until I get 4 values, these 4 values are selected by using the VBA RandBetween function.
  • A For Loop extracts the values from the list where we used an IF statement to check if the cells contain values or not.
  • Another For Loop places those extracted random selections in the selected cell.

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

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

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.

Embed VBA to Select Random Names One by One from a List

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.

Embed VBA to Select Random Names One by One from a List

Code Breakdown

  • We created a Sub procedure SelectNames_OneByOne.
  • Declared two variablesxSource, xDestination As Range.
  • Range(“B5:B11”) sets the range B5:B11 as source from the active sheet.
  • Range(“F5:F11”) is the destination range in the active sheet.
  • An IF statement checks if the destination range is empty or not within the For Loop.
  • The IF statement within the Nested For Loop picks the Random selection one by one through the Do While loop.

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

Embed VBA to Select Random Names One by One from a List

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

Get FREE Advanced Excel Exercises with Solutions!
Md. Sourov Hossain Mithun
Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun, an Excel and VBA Content Developer at Softeko's ExcelDemy project, joined in October 2021. Holding a Naval Architecture & Marine Engineering degree from BUET, Mithun showcases expertise during his two-year tenure. With over 200 published articles on Excel topics, he earned a promotion to Team Leader, excelling in leading diverse teams. Mithun's passion extends to Advanced Excel, Excel VBA, Data Analysis, and Python programming, contributing significantly to the innovative and dynamic environment of ExcelDemy... Read Full Bio

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

    • 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.
      Applying VBA Macro to find names based on Multiple Data
      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.
      Find Names Based on Multiple Data

      Thanks a ton. Have a good day.
      Regards,
      MD Tanvir Rahman
      Excel and VBA Content Developer
      ExcelDemy, Softeko

  2. Thank you very much. I was struggling to generate 50 or any number of samples from a given population. Seeing your macro, I got the idea and able to generate even 500 random samples from the selected population of 200.
    All credit to you for giving me the inspiration and write the required program.
    Thank you, once again. My program is as follows:

    Sub Random_Sample()

    Dim xRow As Long
    Dim J As Long

    For J = 5 To 55
    Dim I As Long
    For I = 1 To 20
    xRow = [RandBetween(1,200)]
    Cells(I, J) = Cells(xRow + 1, 1)
    Next I
    Next J

    End Sub

    Instructions: My population is entered in (A2:A201). Output will be from 5th column. For more than 50 random samples, add accordingly to 55 like for 70 samples, make J from 5 to 75.I is index for the sample size. For example for 60 random samples, make I = 1 to 60. Good luck to those who wish to utlize this program for their sampling.

    • Reply Lutfor Rahman Shimanto
      Lutfor Rahman Shimanto Dec 26, 2023 at 5:00 PM

      Hello RAMNATH TAKIAR

      Thanks a Ton! for your nice words. Your appreciation means a lot to us. Thank you once again for sharing your expertise with our ExcelDemy blog!

      The extra values you have added prove effective. We appreciate you sharing your program with us, and we believe it will be valuable to others who visit our blog.

      To generate even 500 random samples from the selected population of 200:
      OUTPUT OVERVIEW:

      Excel VBA Sub-procedure (Contributed by RAMNATH TAKIAR):

      
      Sub Random_Sample()
          
          Dim xRow As Long
          Dim J As Long
          
          For J = 5 To 55
              Dim I As Long
              For I = 1 To 20
                  xRow = [RandBetween(1,200)]
                  Cells(I, J) = Cells(xRow + 1, 1)
              Next I
          Next J
      
      End Sub
      

      Regards
      Lutfor Rahman Shimanto
      Excel & VBA Developer
      ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo