Raffle Draw with Multiple Prices

[W1nu]

Hi,
I am making an online raffle drawer for a lottery with multiple prices, and have assembled one based on what I could find on google and YouTube. I have a button with a VBA code which picks winner 1,2,3 etc in consecutive order (see worksheets below). However, I need to remove the ticket for the winner of the previous price before picking the next, so that the same ticket can't be drawn twice. I want this function to be part of the VBA code (attached below) for the button I have made.

With VBA, how can I delete the cell-values in a table-row (without deleting the entire row) based on the ticket that is being drawn?

Before Clicking on Button:

Raffle Draw, Draft.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Random	Rank	Name	Contact Info	#TicketNr
2	0,631992	2	Peter	peter@gmail.com	1
3	0,4750311	4	Kim	kim@gmail.com	2
4	0,3606765	5	Alexander	Alexander@gmail.com	3						Wining Ticket		Winner Name		Contact Info		Price	Winner	Contact Info
5	0,6187869	3	John	john@gmail.com	4						6		craig		craig@gmail.com		1
6	0,0991996	6	Wiley	wiley@gmail.com	5												2
7	0,7894938	1	craig	craig@gmail.com	6												3
8					7												4
9					8
10					9
11					10
12					11
13					12
Entries

Cell Formulas
Range	Formula
A2:A13	A2	=IF(C2="","",RAND())
B2:B13	B2	=IF([@Random]="","",RANK($A2,$A$2:$A$22)+COUNTIF(B1,B1:B1)-1)
K5	K5	=VLOOKUP($Q$5,B2:E7,4,FALSE)
M5	M5	=VLOOKUP($Q$5,B2:E7,2,FALSE)
O5	O5	=VLOOKUP($Q$5,Table2[[Rank]:[Contact Info ]],3,FALSE)

After Clicking on Button:

Raffle Draw, Draft.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Random	Rank	Name	Contact Info	#TicketNr
2	0,1842884	6	Peter	peter@gmail.com	1
3	0,8217653	2	Kim	kim@gmail.com	2
4	0,2215632	5	Alexander	Alexander@gmail.com	3						Wining Ticket		Winner Name		Contact Info		Price	Winner	Contact Info
5	0,5243405	4	John	john@gmail.com	4						5		Wiley		wiley@gmail.com		1	Wiley	wiley@gmail.com
6	0,8244066	1	Wiley	wiley@gmail.com	5												2
7	0,7341799	3	craig	craig@gmail.com	6												3
8					7												4
9					8
10					9
Entries

Cell Formulas
Range	Formula
A2:A10	A2	=IF(C2="","",RAND())
B2:B10	B2	=IF([@Random]="","",RANK($A2,$A$2:$A$22)+COUNTIF(B1,B1:B1)-1)
K5	K5	=VLOOKUP($Q$5,B2:E7,4,FALSE)
M5	M5	=VLOOKUP($Q$5,B2:E7,2,FALSE)
O5	O5	=VLOOKUP($Q$5,Table2[[Rank]:[Contact Info ]],3,FALSE)

VBA:

Sub Draw_New_Winner()

Dim x As Long

For x = 1 To 2000

Calculate

Next x

Application.Calculation = xlCalculationManual
Sheet2.Range("R8").End(xlUp).Offset(1, 0).Value = Sheet2.Range("M5").Value

Sheet2.Range("S8").End(xlUp).Offset(1, 0).Value = Sheet2.Range("O5").Value

End Sub