Deleting certain rows if golfer not playing

[mazdaq100]

I've downloaded some stats on Driving Distance from the PGA website - there's 217 players in the list. I now want to delete those players that aren't playing in this week's tournament. I have the entry list of players competing in the tournament in another column in the sheet. Any ideas on how to do this? For example:

						Entry List
RANK THIS WEEK	PLAYER First name	Surname	AVG.	TOTAL DISTANCE	TOTAL DRIVES	Surname	First name
1​	Bryson	DeChambeau	323​	27134​	84​	An	Byeong Hun
2​	Cameron	Champ	321.8​	32177​	100​	Ancer	Abraham
3​	Ryan	Brehm	314.7​	27695​	88​	Berger	Daniel
4​	Grayson	Murray	314.1​	21360​	68​	Bezuidenhout	Christiaan
5​	Rory	McIlroy	313.9​	17581​	56​	Blair	Zac
6​	Bubba	Watson	313.1​	22541​	72​	Bradley	Keegan
7​	Matthew	Wolff	312.6​	26262​	84​	Cabrera Bello	Rafa
8​	Brandon	Hagy	312.3​	24980​	80​	Cantlay	Patrick
9​	Jason	Kokrak	311.9​	21831​	70​	Casey	Paul
10​	Sam	Burns	311.1​	29865​	96​	Cauley	Bud
11​	Scottie	Scheffler	310.8​	36054​	116​	Champ	Cameron
12​	Adam	Scott	310.3​	11172​	36​	Choi	K.J.
13​	Tyler	McCumber	309.8​	31598​	102​	Cink	Stewart
14​	Tommy	Fleetwood	309.5​	6189​	20​	Clark	Wyndham
15​	Brooks	Koepka	308.4​	13570​	44​	Conners	Corey
16​	Cameron	Davis	308.3​	28363​	92​	Dahmen	Joel
17​	Patrick	Rodgers	308.1​	46220​	150​	Day	Jason
18​	Jhonattan	Vegas	308​	25870​	84​	DeChambeau	Bryson
T19	Gary	Woodland	307.5​	22143​	72​	Dufner	Jason
T19	Lucas	Bjerregaard	307.5​	14147​	46​	Duncan	Tyler
21​	Byeong Hun	An	307​	28854​	94​	Els	Ernie
22​	Scott	Harrington	306.8​	34366​	112​	English	Harris
23​	Will	Gordon	306.6​	15944​	52​	Finau	Tony
T24	Beau	Hossler	306.3​	36142​	118​	Fitzpatrick	Matthew
T24	Dustin	Johnson	306.3​	15928​	52​	Fowler	Rickie
26​	Tony	Finau	305.9​	23250​	76​	Frittelli	Dylan
27​	Sergio	Garcia	305.7​	11006​	36​	Furyk	Jim

 

[mazdaq100]

So, for example, number 3 on the driving list is Ryan Brehm but he isn't playing this week, so I would like to delete his entry from the driving distance stats.

 

[Peter_SSs]

You may have to tweak this a little to match the code to your exact layout, but see if this is what you want.
Test with a copy of your data.
I have assumed column Z is unused.

Sub DeleteNonStarters()
  Dim rCrit As Range, rDel As Range
  Dim lrFull As Long, lrThisWeek As Long
  
  Application.ScreenUpdating = False
  lrFull = Range("A" & Rows.Count).End(xlUp).Row
  lrThisWeek = Range("H" & Rows.Count).End(xlUp).Row
  Set rCrit = Range("Z1:Z2")
  rCrit.Cells(2).Formula = Replace("=COUNTIFS(H$2:H$#,C2,I$2:I$#,B2)=0", "#", lrThisWeek)
  With Range("A1:F" & lrFull)
    .AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=rCrit, Unique:=False
    Set rDel = .Offset(1).SpecialCells(xlVisible)
  End With
  On Error Resume Next
  ActiveSheet.ShowAllData
  On Error GoTo 0
  rDel.Delete Shift:=xlUp
  rCrit.ClearContents
  Application.ScreenUpdating = True
End Sub

My layout:

mazdaq100 2020-07-13 1.xlsm
	A	B	C	D	E	F	G	H	I
1	RANK THIS WEEK	PLAYER First name	Surname	AVG.	TOTAL DISTANCE	TOTAL DRIVES		Surname	First name
2	1	Bryson	DeChambeau	323	27134	84		An	Byeong Hun
3	2	Cameron	Champ	321.8	32177	100		Ancer	Abraham
4	3	Ryan	Brehm	314.7	27695	88		Berger	Daniel
5	4	Grayson	Murray	314.1	21360	68		Bezuidenhout	Christiaan
6	5	Rory	McIlroy	313.9	17581	56		Blair	Zac
7	6	Bubba	Watson	313.1	22541	72		Bradley	Keegan
8	7	Matthew	Wolff	312.6	26262	84		Cabrera Bello	Rafa
9	8	Brandon	Hagy	312.3	24980	80		Cantlay	Patrick
10	9	Jason	Kokrak	311.9	21831	70		Casey	Paul
11	10	Sam	Burns	311.1	29865	96		Cauley	Bud
12	11	Scottie	Scheffler	310.8	36054	116		Champ	Cameron
13	12	Adam	Scott	310.3	11172	36		Choi	K.J.
14	13	Tyler	McCumber	309.8	31598	102		Cink	Stewart
15	14	Tommy	Fleetwood	309.5	6189	20		Clark	Wyndham
16	15	Brooks	Koepka	308.4	13570	44		Conners	Corey
17	16	Cameron	Davis	308.3	28363	92		Dahmen	Joel
18	17	Patrick	Rodgers	308.1	46220	150		Day	Jason
19	18	Jhonattan	Vegas	308	25870	84		DeChambeau	Bryson
20	T19	Gary	Woodland	307.5	22143	72		Dufner	Jason
21	T19	Lucas	Bjerregaard	307.5	14147	46		Duncan	Tyler
22	21	Byeong Hun	An	307	28854	94		Els	Ernie
23	22	Scott	Harrington	306.8	34366	112		English	Harris
24	23	Will	Gordon	306.6	15944	52		Finau	Tony
25	T24	Beau	Hossler	306.3	36142	118		Fitzpatrick	Matthew
26	T24	Dustin	Johnson	306.3	15928	52		Fowler	Rickie
27	26	Tony	Finau	305.9	23250	76		Frittelli	Dylan
28	27	Sergio	Garcia	305.7	11006	36		Furyk	Jim
29
Sheet2

After the code has run:

mazdaq100 2020-07-13 1.xlsm
	A	B	C	D	E	F	G	H	I
1	RANK THIS WEEK	PLAYER First name	Surname	AVG.	TOTAL DISTANCE	TOTAL DRIVES		Surname	First name
2	1	Bryson	DeChambeau	323	27134	84		An	Byeong Hun
3	2	Cameron	Champ	321.8	32177	100		Ancer	Abraham
4	21	Byeong Hun	An	307	28854	94		Berger	Daniel
5	26	Tony	Finau	305.9	23250	76		Bezuidenhout	Christiaan
6								Blair	Zac
7								Bradley	Keegan
8								Cabrera Bello	Rafa
9								Cantlay	Patrick
10								Casey	Paul
11								Cauley	Bud
12								Champ	Cameron
13								Choi	K.J.
14								Cink	Stewart
15								Clark	Wyndham
16								Conners	Corey
17								Dahmen	Joel
18								Day	Jason
19								DeChambeau	Bryson
20								Dufner	Jason
21								Duncan	Tyler
22								Els	Ernie
23								English	Harris
24								Finau	Tony
25								Fitzpatrick	Matthew
26								Fowler	Rickie
27								Frittelli	Dylan
28								Furyk	Jim
29
Sheet2

 

[mazdaq100]

Hi Peter

Thanks so much for this - it worked as per your example. However, one problem I have now - I'm trying to apply the macro to the whole list of players - the example I posted above was just a fraction of the whole list. I have a list of 217 players in driving stats and the entry list for the tournament is 133 players. When I apply the macro to another sheet with the complete lists, all 217 players are deleted.

I seem to have the spreadsheet set up in the same way as you posted above. Unfortunately, I have no experience with VBA - I've looked through the code to try and work what needs changing but I can't figure it out. Could you advise on how to tweak the code please?

Sorry for not posting the whole example originally, just didn't want to take up too much space. Full data posted below.

Thanks.

 

[mazdaq100]

200713 driving distance example.xlsx
	A	B	C	D	E	F	G	H	I
1	RANK THIS WEEK	PLAYER First name	Surname	AVG.	TOTAL DISTANCE	TOTAL DRIVES		Surname	First name
2	1	Bryson	DeChambeau	323	27134	84		An	Byeong Hun
3	2	Cameron	Champ	321.8	32177	100		Ancer	Abraham
4	3	Ryan	Brehm	314.7	27695	88		Berger	Daniel
5	4	Grayson	Murray	314.1	21360	68		Bezuidenhout	Christiaan
6	5	Rory	McIlroy	313.9	17581	56		Blair	Zac
7	6	Bubba	Watson	313.1	22541	72		Bradley	Keegan
8	7	Matthew	Wolff	312.6	26262	84		Cabrera Bello	Rafa
9	8	Brandon	Hagy	312.3	24980	80		Cantlay	Patrick
10	9	Jason	Kokrak	311.9	21831	70		Casey	Paul
11	10	Sam	Burns	311.1	29865	96		Cauley	Bud
12	11	Scottie	Scheffler	310.8	36054	116		Champ	Cameron
13	12	Adam	Scott	310.3	11172	36		Choi	K.J.
14	13	Tyler	McCumber	309.8	31598	102		Cink	Stewart
15	14	Tommy	Fleetwood	309.5	6189	20		Clark	Wyndham
16	15	Brooks	Koepka	308.4	13570	44		Conners	Corey
17	16	Cameron	Davis	308.3	28363	92		Dahmen	Joel
18	17	Patrick	Rodgers	308.1	46220	150		Day	Jason
19	18	Jhonattan	Vegas	308	25870	84		DeChambeau	Bryson
20	T19	Gary	Woodland	307.5	22143	72		Dufner	Jason
21	T19	Lucas	Bjerregaard	307.5	14147	46		Duncan	Tyler
22	21	Byeong Hun	An	307	28854	94		Els	Ernie
23	22	Scott	Harrington	306.8	34366	112		English	Harris
24	23	Will	Gordon	306.6	15944	52		Finau	Tony
25	T24	Beau	Hossler	306.3	36142	118		Fitzpatrick	Matthew
26	T24	Dustin	Johnson	306.3	15928	52		Fowler	Rickie
27	26	Tony	Finau	305.9	23250	76		Frittelli	Dylan
28	27	Sergio	Garcia	305.7	11006	36		Furyk	Jim
29	T28	Hideki	Matsuyama	305.3	24423	80		Garcia	Sergio
30	T28	Jon	Rahm	305.3	20762	68		Glover	Lucas
31	30	Xander	Schauffele	305.1	23190	76		Gooch	Talor
32	31	Kristoffer	Ventura	304.8	23167	76		Grace	Branden
33	T32	Matt	Wallace	304.7	15844	52		Griffin	Lanto
34	T32	Sung	Kang	304.7	29862	98		Grillo	Emiliano
35	T32	Luke	List	304.7	28645	94		Haas	Bill
36	T35	Seamus	Power	304.6	15838	52		Hadwin	Adam
37	T35	Wyndham	Clark	304.6	24979	82		Harman	Brian
38	37	Danny	Lee	304.3	25562	84		Harrington	Scott
39	38	Paul	Casey	304	14590	48		Herman	Jim
40	39	Kevin	Tway	303.9	19451	64		Higgs	Harry
41	40	Lanto	Griffin	303.8	43742	144		Hoag	Bo
42	41	Jordan	Spieth	303.6	19430	64		Hoge	Tom
43	42	Sebastian	Cappelen	303.4	29732	98		Homa	Max
44	43	Sebastián	Muñoz	303.2	37596	124		Horschel	Billy
45	44	Phil	Mickelson	302.9	17567	58		Hovland	Viktor
46	T45	Zack	Sucher	302.8	20588	68		Howell III	Charles
47	T45	Danny	Willett	302.8	12111	40		Hubbard	Mark
48	T45	Brendan	Steele	302.8	29072	96		Hughes	Mackenzie
49	48	Harold	Varner III	302.6	32681	108		Im	Sungjae
50	T49	Rafael	Campos	302.2	17527	58		Janewattananond	Jazz
51	T49	Cameron	Tringale	302.2	32633	108		Johnson	Dustin
52	T49	Lucas	Glover	302.2	25985	86		Johnson	Zach
53	T52	Martin	Laird	302.1	23562	78		Kang	Sung
54	T52	Charles	Howell III	302.1	23567	78		Kim	Si Woo
55	T52	Cameron	Smith	302.1	19332	64		Kisner	Kevin
56	T55	Justin	Thomas	302	20538	68		Koepka	Brooks
57	T55	Peter	Uihlein	302	21142	70		Kokrak	Jason
58	57	Nick	Watney	301.8	22934	76		Kuchar	Matt
59	T58	Patrick	Cantlay	301.5	16886	56		Kuest	Peter
60	T58	Sepp	Straka	301.5	32565	108		Landry	Andrew
61	T60	Sungjae	Im	301.4	36163	120		Lashley	Nate
62	T60	Martin	Trainer	301.4	25317	84		Lee	Danny
63	62	Hudson	Swafford	301.2	17470	58		Lehman	Tom
64	63	Keegan	Bradley	301	27688	92		Leishman	Marc
65	T64	Harris	English	300.8	28874	96		Li	Haotong
66	T64	Adam	Schenk	300.8	32490	108		Lingmerth	David
67	T64	Aaron	Wise	300.8	24667	82		Long	Adam
68	67	Kevin	Chappell	300.6	24649	82		Lowry	Shane
69	68	Harry	Higgs	300.5	41464	138		Matsuyama	Hideki
70	T69	J.B.	Holmes	300.3	13814	46		McCarthy	Denny
71	T69	Dominic	Bozzelli	300.3	20421	68		McDowell	Graeme
72	T69	Viktor	Hovland	300.3	25227	84		McGirt	William
73	T69	Jazz	Janewattananond	300.3	6005	20		McIlroy	Rory
74	T73	Keith	Mitchell	300.2	24012	80		McNealy	Maverick
75	T73	Vincent	Whaley	300.2	19212	64		Merritt	Troy
76	75	Vince	Covello	300.1	12604	42		Mickelson	Phil
77	76	Daniel	Berger	299.9	22792	76		Mitchell	Keith
78	77	Marc	Leishman	299.8	19786	66		Moore	Ryan
79	T78	Carlos	Ortiz	299.5	34739	116		Morikawa	Collin
80	T78	Shawn	Stefani	299.5	16772	56		Muñoz	Sebastián
81	80	Abraham	Ancer	299.4	23949	80		Na	Kevin
82	T81	Joaquin	Niemann	299.1	27517	92		NeSmith	Matthew
83	T81	Billy	Horschel	299.1	24525	82		Niemann	Joaquin
84	83	Rhein	Gibson	298.9	27499	92		Noren	Alex
85	T84	Jason	Day	298.8	16133	54		Norlander	Henrik
86	T84	Joseph	Bramlett	298.8	29287	98		Ogletree	Andy
87	86	Nelson	Ledesma	298.5	21493	72		Oosthuizen	Louis
88	T87	Jimmy	Walker	298.4	23873	80		Ortiz	Carlos
89	T87	Webb	Simpson	298.4	17902	60		Palmer	Ryan
90	89	Si Woo	Kim	298.3	27444	92		Pan	C.T.
91	T90	Shane	Lowry	298.1	11923	40		Perez	Victor
92	T90	Scott	Piercy	298.1	25042	84		Pettersson	Carl
93	T90	D.J.	Trahan	298.1	25640	86		Piercy	Scott
94	T93	Matt	Jones	298	34564	116		Poston	J.T.
95	T93	Corey	Conners	298	25031	84		Poulter	Ian
96	T95	Richy	Werenski	297.9	25021	84		Putnam	Andrew
97	T95	Mackenzie	Hughes	297.9	27408	92		Rahm	Jon
98	T97	Tyrrell	Hatton	297.8	9529	32		Reavie	Chez
99	T97	Sam	Ryder	297.8	38122	128		Redman	Doc
100	99	Ryan	Palmer	297.7	21435	72		Reed	Patrick
101	100	Louis	Oosthuizen	297.6	11902	40		Rodgers	Patrick
102	101	Justin	Rose	297.5	13090	44		Rose	Justin
103	T102	Scott	Stallings	297.4	33902	114		Sabbatini	Rory
104	T102	Talor	Gooch	297.4	37470	126		Schauffele	Xander
105	T104	Doc	Redman	297.3	40438	136		Scheffler	Scottie
106	T104	Branden	Grace	297.3	18435	62		Schwab	Matthias
107	106	J.J.	Spaun	297.2	32102	108		Schwartzel	Charl
108	107	Michael	Gligic	296.9	23752	80		Scrivener	Jason
109	T108	Charley	Hoffman	296.7	27893	94		Simpson	Webb
110	T108	Collin	Morikawa	296.7	29673	100		Singh	Vijay
111	110	Kevin	Streelman	296.6	26694	90		Smith	Cameron
112	111	Nick	Taylor	296.5	26689	90		Snedeker	Brandt
113	112	Stewart	Cink	296.3	24294	82		Spieth	Jordan
114	113	Russell	Knox	296	33740	114		Steele	Brendan
115	114	Max	Homa	295.9	26039	88		Straka	Sepp
116	T115	Maverick	McNealy	295.8	37860	128		Streelman	Kevin
117	T115	Alex	Noren	295.8	22477	76		Stricker	Steve
118	117	Kyoung-Hoon	Lee	295.6	34287	116		Stuard	Brian
119	118	Joel	Dahmen	295.5	31319	106		Taylor	Nick
120	119	Emiliano	Grillo	295.3	25398	86		Taylor	Vaughn
121	120	Cameron	Percy	295.2	24795	84		Thomas	Justin
122	T121	Rickie	Fowler	295.1	18884	64		Todd	Brendon
123	T121	Robby	Shelton	295.1	33646	114		Tway	Kevin
124	T123	Doug	Ghim	295	20060	68		van Rooyen	Erik
125	T123	Matthew	NeSmith	295	34223	116		Varner III	Harold
126	T123	Bo	Hoag	295	31273	106		Walker	Jimmy
127	126	George	McNeill	294.9	15337	52		Wallace	Matt
128	127	Adam	Hadwin	294.8	22997	78		Watson	Bubba
129	128	Chesson	Hadley	294.7	28287	96		Wiesberger	Bernd
130	129	Patrick	Reed	294.6	22390	76		Willett	Danny
131	130	Ben	Taylor	294.5	22381	76		Wolff	Matthew
132	T131	Anirban	Lahiri	294.4	19433	66		Woodland	Gary
133	T131	Jamie	Lovemark	294.4	15310	52		Woods	Tiger
134	133	Josh	Teater	294.2	19420	66		Zhang	Xinjun
135	T134	Jonathan	Byrd	294	14700	50
136	T134	Denny	McCarthy	294	33520	114
137	T134	Hank	Lebioda	294	27633	94
138	137	James	Hahn	293.7	21145	72
139	138	Bronson	Burgoon	293.6	27597	94
140	T139	Austin	Cook	293.4	30518	104
141	T139	Michael	Gellerman	293.4	21124	72
142	141	Robert	Streb	293.3	26980	92
143	142	Xinjun	Zhang	293.2	34600	118
144	143	Patton	Kizzire	293.1	27555	94
145	144	Rafa	Cabrera Bello	292.9	14645	50
146	145	Mark	Anderson	292.8	25181	86
147	146	Bill	Haas	292.7	22242	76
148	T147	Matt	Every	292.6	21656	74
149	T147	Henrik	Norlander	292.6	35110	120
150	149	Brian	Harman	292.5	32764	112
151	T150	Charl	Schwartzel	292.1	12851	44
152	T150	Matthew	Fitzpatrick	292.1	17526	60
153	T150	Kramer	Hickok	292.1	23950	82
154	153	Troy	Merritt	291.8	26848	92
155	154	Bo	van Pelt	291.5	21570	74
156	T155	Wes	Roach	291.3	22141	76
157	T155	Mark	Hubbard	291.3	38449	132
158	157	Ryan	Moore	291.1	15138	52
159	158	Chase	Seiffert	290.8	29084	100
160	159	Roger	Sloan	290.5	26148	90
161	T160	John	Huh	290.2	15089	52
162	T160	Bud	Cauley	290.2	27282	94
163	T160	Brice	Garnett	290.2	29020	100
164	T160	Adam	Long	290.2	33667	116
165	T164	Dylan	Frittelli	289.7	30129	104
166	T164	Ted	Potter Jr.	289.7	23173	80
167	166	Brandt	Snedeker	288.9	22537	78
168	T167	John	Senden	288.6	15584	54
169	T167	Pat	Perez	288.6	23667	82
170	T167	Fabián	Gómez	288.6	29433	102
171	T167	Russell	Henley	288.6	25970	90
172	T167	Chris	Baker	288.6	23663	82
173	172	Kevin	Na	288.5	23658	82
174	173	J.T.	Poston	288.4	28844	100
175	174	Chris	Kirk	288.2	14408	50
176	175	Andrew	Landry	288	24767	86
177	T176	Brian	Gay	287.9	32815	114
178	T176	Tom	Hoge	287.9	31096	108
179	178	Scott	Brown	287.5	32202	112
180	179	Michael	Thompson	287.2	26420	92
181	180	Nate	Lashley	287	21236	74
182	181	Chris	Stroud	286.9	25246	88
183	182	Kevin	Kisner	286.8	22372	78
184	T183	Arjun	Atwal	286.6	14332	50
185	T183	Ian	Poulter	286.6	13758	48
186	185	Peter	Malnati	286.1	29187	102
187	T186	Zach	Johnson	285.4	26823	94
188	T186	Jason	Dufner	285.4	27402	96
189	188	C.T.	Pan	285.3	19402	68
190	189	J.J.	Henry	285.2	13118	46
191	190	Ricky	Barnes	285.1	17107	60
192	191	Aaron	Baddeley	285	23370	82
193	192	Johnson	Wagner	284.9	13107	46
194	T193	Tim	Wilkinson	284.5	22756	80
195	T193	Michael	Kim	284.5	10243	36
196	T195	Ryan	Armour	284.1	28413	100
197	T195	Chez	Reavie	284.1	26707	94
198	T197	Jim	Herman	284	24988	88
199	T197	Roberto	Castro	284	14202	50
200	199	Rob	Oppenheim	283.9	31227	110
201	T200	Graeme	McDowell	283.8	17597	62
202	T200	Kyle	Stanley	283.8	26674	94
203	T200	Tyler	Duncan	283.8	34622	122
204	T203	Kevin	Stadler	283.4	12468	44
205	T203	Rory	Sabbatini	283.4	27209	96
206	205	Alex	Cejka	282.6	12999	46
207	206	Matt	Kuchar	281.9	21422	76
208	207	Vaughn	Taylor	281.8	27615	98
209	208	Brian	Stuard	281.7	37748	134
210	209	David	Hearn	281.4	26454	94
211	210	Jim	Furyk	281	17425	62
212	211	Brendon	Todd	280.8	33701	120
213	212	Ben	Martin	280.5	16830	60
214	213	Zac	Blair	279.9	33585	120
215	214	Luke	Donald	279.3	16760	60
216	215	Steve	Stricker	279.1	14515	52
217	216	Andrew	Putnam	277	15513	56
218	217	Satoshi	Kodaira	276.8	14946	54
Sheet

 

[Peter_SSs]

When I apply the macro to another sheet with the complete lists, all 217 players are deleted.

I seem to have the spreadsheet set up in the same way as you posted above. Unfortunately, I have no experience with VBA - I've looked through the code to try and work what needs changing but I can't figure it out. Could you advise on how to tweak the code please?

If you look at column I you will see that there is a space before every 'First name' whereas in column B there is not. Therefore, the code assesses (quite correctly) that none of the full list of players is playing this week.

Try adding this line into the code where shown

  lrThisWeek = Range("H" & Rows.Count).End(xlUp).Row
  Range("I2:I" & lrThisWeek).Value = Evaluate("trim(I2:I" & lrThisWeek & ")")
  Set rCrit = Range("Z1:Z2")

Sorry for not posting the whole example originally, just didn't want to take up too much space. Full data posted below.

You did exactly the right thing in your earlier post in relation to size of sample. In general, we do not need or want huge data samples. What is important is that the sample is representative.

 

[mazdaq100]

Works perfectly, thank you Peter! Very grateful to you.

 

[Peter_SSs]

Works perfectly, thank you Peter! Very grateful to you.

You are welcome. Thanks for the confirmation.