Delete Rows Based On A Variable Cell Value - Xmas Prize Drawv

[TTACM88]

Hi Everyone,

I'm building a random name generator for a series of charity prize draws and I'm having a few problems perfecting my code.

Basically, I have some VBA code that will search through a single column of names (column a) then select a name at random from the list before displaying the 'winner' is a specific cell - say D6.

In terms of the code I have below, this code all works fine and does what I need it too.

The problem I now have is that after the first draw has taken place, I want to repeat the process on a daily basis but remove the previous winner from the list (I only want to give out one prize per individual person).

However, from looking around on the web, and this forum, lots of information is available for how to get delete rows VBA functionality to work, but all with hard-coded pre-defined values. Obviously, I don't want that because every time the Macro is run I need to remove the last winner, which will then be different on the next run.

I was thinking the best way to do this is to somehow store the output of the previous draw as a variable, call it at the start of the next Marco run and delete that name from the list before the next name is pulled out. However, I
can't even seem to find the correct syntax to call the value of a cell, let alone fathom how to control that.

Is there any way to do this with VBA and Excel (I'm using Microsoft Office Professional Plus 2013)?

Any help would be really appreciated as I'm not an expert in VBA.

The code I'm using is below:

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 = 1
CellsOut = 6


' CellsOut references the row


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(9, NoOfNames + 100)
' Check to see if the name has already been picked
' The first digit after the bracket determines the start cell in the range, so a 2 would start after a header
' The number after the + references the length of the list
' if you set it to one it will scroll indefinitely but only without gaps in the sequence
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


' Number 4 references the column, so with the cells out value impacts locations


Next ArI


Application.ScreenUpdating = True


End Sub

 

[MickG]

Perhaps you could use this for the whole thing ???

[COLOR="Navy"]Sub[/COLOR] MG22Nov21
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Rw [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Randomize
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
 Rw = Application.RandBetween(1, Rng.Count)
   Range("d6").Value = Range("A" & Rw).Value
     Range("A" & Rw).Delete
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[TTACM88]

Perhaps you could use this for the whole thing ???

[COLOR=Navy]Sub[/COLOR] MG22Nov21
[COLOR=Navy]Dim[/COLOR] Rng [COLOR=Navy]As[/COLOR] Range, Rw [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
Randomize
[COLOR=Navy]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
 Rw = Application.RandBetween(1, Rng.Count)
   Range("d6").Value = Range("A" & Rw).Value
     Range("A" & Rw).Delete
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Regards Mick

Mick - this is brilliant!! Much simpler and more elegant code.
Thanks very much!!

 

[MickG]

You,re welcome