Deleting certain rows if golfer not playing

mazdaq100

New Member
Joined
Apr 7, 2010
Messages
19
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 WEEKPLAYER First nameSurnameAVG.TOTAL DISTANCETOTAL DRIVESSurnameFirst name
1​
BrysonDeChambeau
323​
27134​
84​
AnByeong Hun
2​
CameronChamp
321.8​
32177​
100​
AncerAbraham
3​
RyanBrehm
314.7​
27695​
88​
BergerDaniel
4​
GraysonMurray
314.1​
21360​
68​
BezuidenhoutChristiaan
5​
RoryMcIlroy
313.9​
17581​
56​
BlairZac
6​
BubbaWatson
313.1​
22541​
72​
BradleyKeegan
7​
MatthewWolff
312.6​
26262​
84​
Cabrera BelloRafa
8​
BrandonHagy
312.3​
24980​
80​
CantlayPatrick
9​
JasonKokrak
311.9​
21831​
70​
CaseyPaul
10​
SamBurns
311.1​
29865​
96​
CauleyBud
11​
ScottieScheffler
310.8​
36054​
116​
ChampCameron
12​
AdamScott
310.3​
11172​
36​
ChoiK.J.
13​
TylerMcCumber
309.8​
31598​
102​
CinkStewart
14​
TommyFleetwood
309.5​
6189​
20​
ClarkWyndham
15​
BrooksKoepka
308.4​
13570​
44​
ConnersCorey
16​
CameronDavis
308.3​
28363​
92​
DahmenJoel
17​
PatrickRodgers
308.1​
46220​
150​
DayJason
18​
JhonattanVegas
308​
25870​
84​
DeChambeauBryson
T19GaryWoodland
307.5​
22143​
72​
DufnerJason
T19LucasBjerregaard
307.5​
14147​
46​
DuncanTyler
21​
Byeong HunAn
307​
28854​
94​
ElsErnie
22​
ScottHarrington
306.8​
34366​
112​
EnglishHarris
23​
WillGordon
306.6​
15944​
52​
FinauTony
T24BeauHossler
306.3​
36142​
118​
FitzpatrickMatthew
T24DustinJohnson
306.3​
15928​
52​
FowlerRickie
26​
TonyFinau
305.9​
23250​
76​
FrittelliDylan
27​
SergioGarcia
305.7​
11006​
36​
FurykJim
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

mazdaq100

New Member
Joined
Apr 7, 2010
Messages
19
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,064
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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
ABCDEFGHI
1RANK THIS WEEKPLAYER First nameSurnameAVG.TOTAL DISTANCETOTAL DRIVESSurnameFirst name
21BrysonDeChambeau3232713484AnByeong Hun
32CameronChamp321.832177100AncerAbraham
43RyanBrehm314.72769588BergerDaniel
54GraysonMurray314.12136068BezuidenhoutChristiaan
65RoryMcIlroy313.91758156BlairZac
76BubbaWatson313.12254172BradleyKeegan
87MatthewWolff312.62626284Cabrera BelloRafa
98BrandonHagy312.32498080CantlayPatrick
109JasonKokrak311.92183170CaseyPaul
1110SamBurns311.12986596CauleyBud
1211ScottieScheffler310.836054116ChampCameron
1312AdamScott310.31117236ChoiK.J.
1413TylerMcCumber309.831598102CinkStewart
1514TommyFleetwood309.5618920ClarkWyndham
1615BrooksKoepka308.41357044ConnersCorey
1716CameronDavis308.32836392DahmenJoel
1817PatrickRodgers308.146220150DayJason
1918JhonattanVegas3082587084DeChambeauBryson
20T19GaryWoodland307.52214372DufnerJason
21T19LucasBjerregaard307.51414746DuncanTyler
2221Byeong HunAn3072885494ElsErnie
2322ScottHarrington306.834366112EnglishHarris
2423WillGordon306.61594452FinauTony
25T24BeauHossler306.336142118FitzpatrickMatthew
26T24DustinJohnson306.31592852FowlerRickie
2726TonyFinau305.92325076FrittelliDylan
2827SergioGarcia305.71100636FurykJim
29
Sheet2


After the code has run:
mazdaq100 2020-07-13 1.xlsm
ABCDEFGHI
1RANK THIS WEEKPLAYER First nameSurnameAVG.TOTAL DISTANCETOTAL DRIVESSurnameFirst name
21BrysonDeChambeau3232713484AnByeong Hun
32CameronChamp321.832177100AncerAbraham
421Byeong HunAn3072885494BergerDaniel
526TonyFinau305.92325076BezuidenhoutChristiaan
6BlairZac
7BradleyKeegan
8Cabrera BelloRafa
9CantlayPatrick
10CaseyPaul
11CauleyBud
12ChampCameron
13ChoiK.J.
14CinkStewart
15ClarkWyndham
16ConnersCorey
17DahmenJoel
18DayJason
19DeChambeauBryson
20DufnerJason
21DuncanTyler
22ElsErnie
23EnglishHarris
24FinauTony
25FitzpatrickMatthew
26FowlerRickie
27FrittelliDylan
28FurykJim
29
Sheet2
 

mazdaq100

New Member
Joined
Apr 7, 2010
Messages
19
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

New Member
Joined
Apr 7, 2010
Messages
19

ADVERTISEMENT

200713 driving distance example.xlsx
ABCDEFGHI
1RANK THIS WEEKPLAYER First nameSurnameAVG.TOTAL DISTANCETOTAL DRIVESSurnameFirst name
21BrysonDeChambeau3232713484An Byeong Hun
32CameronChamp321.832177100Ancer Abraham
43RyanBrehm314.72769588Berger Daniel
54GraysonMurray314.12136068Bezuidenhout Christiaan
65RoryMcIlroy313.91758156Blair Zac
76BubbaWatson313.12254172Bradley Keegan
87MatthewWolff312.62626284Cabrera Bello Rafa
98BrandonHagy312.32498080Cantlay Patrick
109JasonKokrak311.92183170Casey Paul
1110SamBurns311.12986596Cauley Bud
1211ScottieScheffler310.836054116Champ Cameron
1312AdamScott310.31117236Choi K.J.
1413TylerMcCumber309.831598102Cink Stewart
1514TommyFleetwood309.5618920Clark Wyndham
1615BrooksKoepka308.41357044Conners Corey
1716CameronDavis308.32836392Dahmen Joel
1817PatrickRodgers308.146220150Day Jason
1918JhonattanVegas3082587084DeChambeau Bryson
20T19GaryWoodland307.52214372Dufner Jason
21T19LucasBjerregaard307.51414746Duncan Tyler
2221Byeong HunAn3072885494Els Ernie
2322ScottHarrington306.834366112English Harris
2423WillGordon306.61594452Finau Tony
25T24BeauHossler306.336142118Fitzpatrick Matthew
26T24DustinJohnson306.31592852Fowler Rickie
2726TonyFinau305.92325076Frittelli Dylan
2827SergioGarcia305.71100636Furyk Jim
29T28HidekiMatsuyama305.32442380Garcia Sergio
30T28JonRahm305.32076268Glover Lucas
3130XanderSchauffele305.12319076Gooch Talor
3231KristofferVentura304.82316776Grace Branden
33T32MattWallace304.71584452Griffin Lanto
34T32SungKang304.72986298Grillo Emiliano
35T32LukeList304.72864594Haas Bill
36T35SeamusPower304.61583852Hadwin Adam
37T35WyndhamClark304.62497982Harman Brian
3837DannyLee304.32556284Harrington Scott
3938PaulCasey3041459048Herman Jim
4039KevinTway303.91945164Higgs Harry
4140LantoGriffin303.843742144Hoag Bo
4241JordanSpieth303.61943064Hoge Tom
4342SebastianCappelen303.42973298Homa Max
4443SebastiánMuñoz303.237596124Horschel Billy
4544PhilMickelson302.91756758Hovland Viktor
46T45ZackSucher302.82058868Howell III Charles
47T45DannyWillett302.81211140Hubbard Mark
48T45BrendanSteele302.82907296Hughes Mackenzie
4948HaroldVarner III302.632681108Im Sungjae
50T49RafaelCampos302.21752758Janewattananond Jazz
51T49CameronTringale302.232633108Johnson Dustin
52T49LucasGlover302.22598586Johnson Zach
53T52MartinLaird302.12356278Kang Sung
54T52CharlesHowell III302.12356778Kim Si Woo
55T52CameronSmith302.11933264Kisner Kevin
56T55JustinThomas3022053868Koepka Brooks
57T55PeterUihlein3022114270Kokrak Jason
5857NickWatney301.82293476Kuchar Matt
59T58PatrickCantlay301.51688656Kuest Peter
60T58SeppStraka301.532565108Landry Andrew
61T60SungjaeIm301.436163120Lashley Nate
62T60MartinTrainer301.42531784Lee Danny
6362HudsonSwafford301.21747058Lehman Tom
6463KeeganBradley3012768892Leishman Marc
65T64HarrisEnglish300.82887496Li Haotong
66T64AdamSchenk300.832490108Lingmerth David
67T64AaronWise300.82466782Long Adam
6867KevinChappell300.62464982Lowry Shane
6968HarryHiggs300.541464138Matsuyama Hideki
70T69J.B.Holmes300.31381446McCarthy Denny
71T69DominicBozzelli300.32042168McDowell Graeme
72T69ViktorHovland300.32522784McGirt William
73T69JazzJanewattananond300.3600520McIlroy Rory
74T73KeithMitchell300.22401280McNealy Maverick
75T73VincentWhaley300.21921264Merritt Troy
7675VinceCovello300.11260442Mickelson Phil
7776DanielBerger299.92279276Mitchell Keith
7877MarcLeishman299.81978666Moore Ryan
79T78CarlosOrtiz299.534739116Morikawa Collin
80T78ShawnStefani299.51677256Muñoz Sebastián
8180AbrahamAncer299.42394980Na Kevin
82T81JoaquinNiemann299.12751792NeSmith Matthew
83T81BillyHorschel299.12452582Niemann Joaquin
8483RheinGibson298.92749992Noren Alex
85T84JasonDay298.81613354Norlander Henrik
86T84JosephBramlett298.82928798Ogletree Andy
8786NelsonLedesma298.52149372Oosthuizen Louis
88T87JimmyWalker298.42387380Ortiz Carlos
89T87WebbSimpson298.41790260Palmer Ryan
9089Si WooKim298.32744492Pan C.T.
91T90ShaneLowry298.11192340Perez Victor
92T90ScottPiercy298.12504284Pettersson Carl
93T90D.J.Trahan298.12564086Piercy Scott
94T93MattJones29834564116Poston J.T.
95T93CoreyConners2982503184Poulter Ian
96T95RichyWerenski297.92502184Putnam Andrew
97T95MackenzieHughes297.92740892Rahm Jon
98T97TyrrellHatton297.8952932Reavie Chez
99T97SamRyder297.838122128Redman Doc
10099RyanPalmer297.72143572Reed Patrick
101100LouisOosthuizen297.61190240Rodgers Patrick
102101JustinRose297.51309044Rose Justin
103T102ScottStallings297.433902114Sabbatini Rory
104T102TalorGooch297.437470126Schauffele Xander
105T104DocRedman297.340438136Scheffler Scottie
106T104BrandenGrace297.31843562Schwab Matthias
107106J.J.Spaun297.232102108Schwartzel Charl
108107MichaelGligic296.92375280Scrivener Jason
109T108CharleyHoffman296.72789394Simpson Webb
110T108CollinMorikawa296.729673100Singh Vijay
111110KevinStreelman296.62669490Smith Cameron
112111NickTaylor296.52668990Snedeker Brandt
113112StewartCink296.32429482Spieth Jordan
114113RussellKnox29633740114Steele Brendan
115114MaxHoma295.92603988Straka Sepp
116T115MaverickMcNealy295.837860128Streelman Kevin
117T115AlexNoren295.82247776Stricker Steve
118117Kyoung-HoonLee295.634287116Stuard Brian
119118JoelDahmen295.531319106Taylor Nick
120119EmilianoGrillo295.32539886Taylor Vaughn
121120CameronPercy295.22479584Thomas Justin
122T121RickieFowler295.11888464Todd Brendon
123T121RobbyShelton295.133646114Tway Kevin
124T123DougGhim2952006068van Rooyen Erik
125T123MatthewNeSmith29534223116Varner III Harold
126T123BoHoag29531273106Walker Jimmy
127126GeorgeMcNeill294.91533752Wallace Matt
128127AdamHadwin294.82299778Watson Bubba
129128ChessonHadley294.72828796Wiesberger Bernd
130129PatrickReed294.62239076Willett Danny
131130BenTaylor294.52238176Wolff Matthew
132T131AnirbanLahiri294.41943366Woodland Gary
133T131JamieLovemark294.41531052Woods Tiger
134133JoshTeater294.21942066Zhang Xinjun
135T134JonathanByrd2941470050
136T134DennyMcCarthy29433520114
137T134HankLebioda2942763394
138137JamesHahn293.72114572
139138BronsonBurgoon293.62759794
140T139AustinCook293.430518104
141T139MichaelGellerman293.42112472
142141RobertStreb293.32698092
143142XinjunZhang293.234600118
144143PattonKizzire293.12755594
145144RafaCabrera Bello292.91464550
146145MarkAnderson292.82518186
147146BillHaas292.72224276
148T147MattEvery292.62165674
149T147HenrikNorlander292.635110120
150149BrianHarman292.532764112
151T150CharlSchwartzel292.11285144
152T150MatthewFitzpatrick292.11752660
153T150KramerHickok292.12395082
154153TroyMerritt291.82684892
155154Bovan Pelt291.52157074
156T155WesRoach291.32214176
157T155MarkHubbard291.338449132
158157RyanMoore291.11513852
159158ChaseSeiffert290.829084100
160159RogerSloan290.52614890
161T160JohnHuh290.21508952
162T160BudCauley290.22728294
163T160BriceGarnett290.229020100
164T160AdamLong290.233667116
165T164DylanFrittelli289.730129104
166T164TedPotter Jr.289.72317380
167166BrandtSnedeker288.92253778
168T167JohnSenden288.61558454
169T167PatPerez288.62366782
170T167FabiánGómez288.629433102
171T167RussellHenley288.62597090
172T167ChrisBaker288.62366382
173172KevinNa288.52365882
174173J.T.Poston288.428844100
175174ChrisKirk288.21440850
176175AndrewLandry2882476786
177T176BrianGay287.932815114
178T176TomHoge287.931096108
179178ScottBrown287.532202112
180179MichaelThompson287.22642092
181180NateLashley2872123674
182181ChrisStroud286.92524688
183182KevinKisner286.82237278
184T183ArjunAtwal286.61433250
185T183IanPoulter286.61375848
186185PeterMalnati286.129187102
187T186ZachJohnson285.42682394
188T186JasonDufner285.42740296
189188C.T.Pan285.31940268
190189J.J.Henry285.21311846
191190RickyBarnes285.11710760
192191AaronBaddeley2852337082
193192JohnsonWagner284.91310746
194T193TimWilkinson284.52275680
195T193MichaelKim284.51024336
196T195RyanArmour284.128413100
197T195ChezReavie284.12670794
198T197JimHerman2842498888
199T197RobertoCastro2841420250
200199RobOppenheim283.931227110
201T200GraemeMcDowell283.81759762
202T200KyleStanley283.82667494
203T200TylerDuncan283.834622122
204T203KevinStadler283.41246844
205T203RorySabbatini283.42720996
206205AlexCejka282.61299946
207206MattKuchar281.92142276
208207VaughnTaylor281.82761598
209208BrianStuard281.737748134
210209DavidHearn281.42645494
211210JimFuryk2811742562
212211BrendonTodd280.833701120
213212BenMartin280.51683060
214213ZacBlair279.933585120
215214LukeDonald279.31676060
216215SteveStricker279.11451552
217216AndrewPutnam2771551356
218217SatoshiKodaira276.81494654
Sheet
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,064
Office Version
  1. 365
Platform
  1. Windows
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

Rich (BB code):
  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.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,671
Messages
5,654,664
Members
418,147
Latest member
dorkas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top