# Pick Random From List, Then Delete



## redhots4 (Jan 5, 2023)

Hi. I have a list of 365 writing prompts, one for each day of the year. I want to randomly select a number (1-365), then look up the prompt for that number. Once that prompt is picked, I want to delete its corresponding number from the list of available numbers so it doesn't get picked again. Anyone done this or have a good solution?


----------



## Micron (Jan 5, 2023)

There must be a half-dozen ways to get a random value from a list. Did you look at any of them? Here's one method:








						VBA Random selection from list of names
					

Hi All, I'm working on a spreadsheet that I would like to pick a random name from a list of 10 names using a vba button.  the list of names are in cells C3:C12 and I would like the selected name to appear in cell D3  Thanks in advance.




					www.mrexcel.com
				




Not sure what "I want" means here. It might mean that you will manually look up and then delete from the list.


----------



## redhots4 (Jan 5, 2023)

Micron said:


> There must be a half-dozen ways to get a random value from a list. Did you look at any of them? Here's one method:
> 
> 
> 
> ...


Thanks for the reply. I'm well versed in picking random numbers between lower and upper limits. What I need here is (a.) the ability to click a button to pick a number randomly from a list of previously-unused numbers and (b) the ability to instruct Excel to delete a picked number from the list of remaining numbers so it isn't picked again. Deleting the used numbers gives me the ability to visually see the list of prompts dwindling over time.


----------



## Peter_SSs (Jan 5, 2023)

To understand better and for testing, could we reduce the initial number to 10 instead of 365, post that sample data with XL2BB so that we can see the layout and copy for testing? You might further explain in words, just what would happen on the sheet after each pick.


----------



## Micron (Jan 5, 2023)

redhots4 said:


> I'm well versed in picking random numbers between lower and upper limits


If by that you mean between x and y numbers, that's not what I intended to suggest by the link. It was supposed to suggest how to choose a value from a list of values. In effect, they're not really "unused" when values are being removed. They either exist in the list, or not. Once randomly chosen and the prompt has been looked up, the code deletes the list value, I guess. I can't see how generating a random number between 1 and 365 and looking that up in a list will ever work if anyone suggests that, because you'll eventually generate values that don't exist in the list. Hence, picking a random list value is part of the solution I'd say.


----------



## redhots4 (Jan 5, 2023)

Peter_SSs said:


> To understand better and for testing, could we reduce the initial number to 10 instead of 365, post that sample data with XL2BB so that we can see the layout and copy for testing? You might further explain in words, just what would happen on the sheet after each pick.


Good idea. Here's a sample sheet with only ten rows. The formula in C2 returns a value at random from column A. How do I tell Excel that the value chosen at random should not be available for selection again? Ideally, I'd like to copy the value in C2 and append it to a list on another tab with the date, so I could see a list of which prompts I randomly selected by date. (Ignore the conditional formatting error).

365 Gratitude Prompts.xlsmABC1ListRandom2Writing Prompt 1Writing Prompt 123Writing Prompt 24Writing Prompt 35Writing Prompt 46Writing Prompt 57Writing Prompt 68Writing Prompt 79Writing Prompt 810Writing Prompt 911Writing Prompt 1012Writing Prompt 1113Writing Prompt 1214Writing Prompt 1315Writing Prompt 1416Writing Prompt 1517Writing Prompt 1618Writing Prompt 1719Writing Prompt 1820Writing Prompt 1921Writing Prompt 2022Writing Prompt 2123Writing Prompt 2224Writing Prompt 2325Writing Prompt 24Sheet1Cell FormulasRangeFormulaC2C2=INDEX(A2:A25,RANDBETWEEN(2,COUNTA(A2:A25)))Cells with Conditional FormattingCellConditionCell FormatStop If TrueA2:A25Expression=#REF!=242textNO


----------



## Peter_SSs (Jan 5, 2023)

redhots4 said:


> Here's a sample sheet with only ten rows.


Hmm, an interesting count of 10! 
.. but thanks for getting XL2BB going. 

_EDIT:
On re-reading, I think that you have answered the following so I will post back in a while._

What about this part


Peter_SSs said:


> You might further explain in words, just what would happen on the sheet after each pick.


.. or else another XL2BB to show what the sheet would look like *after* "Writing Prompt 12" has been used?


----------



## Peter_SSs (Jan 5, 2023)

With Sheet1 as you have shown above. Name a blank sheet in the workbook as 'Log' and try running this code repeatedly.
At any point you can look at the Log sheet to see which prompts have been used and which ones have not been used.


```
Sub Random_Picks()
  Dim wsFull As Worksheet, wsLog As Worksheet
  Dim Pick As Long
  
  Set wsFull = Sheets("Sheet1")
  Set wsLog = Sheets("Log")
  Randomize
  wsFull.Activate
  With wsLog
    If IsEmpty(.Range("A1").Value) Then
      wsFull.Range("A2", wsFull.Range("A" & Rows.Count).End(xlUp)).Copy Destination:=.Range("A2")
      .Range("A1:B1").Value = Array("Unused", "Used")
    End If
    If IsEmpty(.Range("A2").Value) Then
      wsFull.Range("C2").ClearContents
      .UsedRange.ClearContents
      MsgBox "All used. Prompts have been reset. Try again"
    Else
      Pick = 2 + Int(Rnd() * (.Range("A" & Rows.Count).End(xlUp).Row - 1))
      wsFull.Range("C2").Value = .Range("A" & Pick).Value
      .Range("B" & Rows.Count).End(xlUp).Offset(1).Value = .Range("A" & Pick).Value
      .Cells(Pick, "A").Delete Shift:=xlUp
    End If
  End With
End Sub
```


----------



## redhots4 (Jan 6, 2023)

Peter_SSs said:


> With Sheet1 as you have shown above. Name a blank sheet in the workbook as 'Log' and try running this code repeatedly.
> At any point you can look at the Log sheet to see which prompts have been used and which ones have not been used.
> 
> 
> ...


Works beautifully, thank you!! Could you make a minor edit to enter the date & time in Column C, alongside "Used" to indicate the date / time each line was added to the Used log?


----------



## Peter_SSs (Jan 7, 2023)

redhots4 said:


> Works beautifully, thank you!! Could you make a minor edit to enter the date & time in Column C, alongside "Used" to indicate the date / time each line was added to the Used log?


Try this


```
Sub Random_Picks_v2()
  Dim wsFull As Worksheet, wsLog As Worksheet
  Dim Pick As Long
  
  Set wsFull = Sheets("Sheet1")
  Set wsLog = Sheets("Log")
  Randomize
  wsFull.Activate
  With wsLog
    If IsEmpty(.Range("A1").Value) Then
      wsFull.Range("A2", wsFull.Range("A" & Rows.Count).End(xlUp)).Copy Destination:=.Range("A2")
      .Range("A1:C1").Value = Array("Unused", "Used", "Date/Time")
    End If
    If IsEmpty(.Range("A2").Value) Then
      wsFull.Range("C2").ClearContents
      .UsedRange.ClearContents
      MsgBox "All used. Prompts have been reset. Try again"
    Else
      Pick = 2 + Int(Rnd() * (.Range("A" & Rows.Count).End(xlUp).Row - 1))
      wsFull.Range("C2").Value = .Range("A" & Pick).Value
      .Range("B" & Rows.Count).End(xlUp).Offset(1).Resize(, 2).Value = Array(.Range("A" & Pick).Value, Now())
      .Cells(Pick, "A").Delete Shift:=xlUp
    End If
  End With
End Sub
```


----------

