Delete based on cell value

cspengel

Board Regular
Joined
Oct 29, 2022
Messages
159
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
I have tried so many different VBA code and it is not working. ANY code I try, it mixes up the rows or the data in general gets all jumbled up. I've tried autofilter, and loops..Nothing. Beyond Frustrating.

I NEED the rows to stay together. What I mean is G2-H2-I2-J2 etc. Some of the code i've tried is shifting the order for some reason.

I need ALL rows to be deleted that contain "1" in the column M.
I need ALL rows to be deleted that contain "0" in column R

My data looks as follows:

Showdown Slate1.xlsm
ABCDEFGHIJKLMNOPQRSTU
1QBFLEXFLEXFLEXFLEXQBFLEXFLEXFLEXFLEXComboIDΣ SalaryΣ ProjectionΣ ValueΣ StackΣ Stack POSΣ CommasΣ FilterΣ Player1Σ Player2
2Dak PrescottDak PrescottDak PrescottDak PrescottDak PrescottDak PrescottCeeDee LambTony PollardJonathan TaylorMatt Ryan2225183.38583385DALQB,FLEX,FLEX30
3CeeDee LambCeeDee LambCeeDee LambCeeDee LambCeeDee LambDak PrescottCeeDee LambTony PollardJonathan TaylorEzekiel Elliott2226183.29583295DALQB,FLEX,FLEX,FLEX00
4Tony PollardTony PollardTony PollardTony PollardTony PollardDak PrescottCeeDee LambTony PollardDalton SchultzBrett Maher2277176.38576385DALQB,FLEX,FLEX,FLEX,FLEX00
5Jonathan TaylorJonathan TaylorJonathan TaylorJonathan TaylorJonathan TaylorDak PrescottCeeDee LambTony PollardDalton SchultzMichael Gallup22786000075.7851.263083333DALQB,FLEX,FLEX,FLEX,FLEX00
6Matt RyanMatt RyanMatt RyanMatt RyanMatt RyanDak PrescottCeeDee LambTony PollardDalton SchultzChase McLaughlin22795950075.4751.268487395DALQB,FLEX,FLEX,FLEX00
7Ezekiel ElliottEzekiel ElliottEzekiel ElliottEzekiel ElliottEzekiel ElliottDak PrescottCeeDee LambTony PollardDalton SchultzDallas Cowboys2280174.67574675DALQB,FLEX,FLEX,FLEX,FLEX00
8Michael Pittman Jr.Michael Pittman Jr.Michael Pittman Jr.Michael Pittman Jr.Michael Pittman Jr.Dak PrescottCeeDee LambTony PollardJake FergusonChase McLaughlin2291169.63569635DALQB,FLEX,FLEX,FLEX00
9Alec PierceDalton SchultzDalton SchultzDak PrescottCeeDee LambTony PollardJake FergusonDallas Cowboys22925900068.8351.166694915DALQB,FLEX,FLEX,FLEX,FLEX00
10Indianapolis ColtsJake FergusonBrett MaherDak PrescottCeeDee LambTony PollardPeyton HendershotJonathan Taylor2296173.54573545DALQB,FLEX,FLEX,FLEX00
11Deon JacksonPeyton HendershotMichael GallupDak PrescottCeeDee LambTony PollardPeyton HendershotMatt Ryan2297172.97572975DALQB,FLEX,FLEX,FLEX00
12Jake FergusonKylen GransonChase McLaughlinDak PrescottCeeDee LambTony PollardKylen GransonDalton Schultz2312169.77569775DALQB,FLEX,FLEX,FLEX00
13Peyton HendershotJelani WoodsDallas CowboysDak PrescottCeeDee LambTony PollardKylen GransonBrett Maher23135900069.5851.17940678DALQB,FLEX,FLEX,FLEX00
14Mo Alie-CoxDak PrescottCeeDee LambTony PollardKylen GransonMichael Gallup2314168.98568985DALQB,FLEX,FLEX,FLEX00
15Dak PrescottCeeDee LambTony PollardKylen GransonChase McLaughlin2315168.67568675DALQB,FLEX,FLEX30
16Dak PrescottCeeDee LambTony PollardKylen GransonDallas Cowboys23165950067.8751.140756303DALQB,FLEX,FLEX,FLEX00
Worksheet
Cell Formulas
RangeFormula
S2:S16S2=IF($U$2="",COUNTIF(G2:K2,$T$2),COUNTIF(G2:K2,$T$2)*COUNTIF(G2:K2,$U$2))
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
58,605
Office Version
  1. 365
Platform
  1. Windows
I need ALL rows to be deleted that contain "1" in the column M.
I need ALL rows to be deleted that contain "0" in column R
The entire row from column A to column XFD, or
Just the part of the row from column G to column U?
 
Upvote 0

cspengel

Board Regular
Joined
Oct 29, 2022
Messages
159
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
The entire row from column A to column XFD, or
Just the part of the row from column G to column U?
I would like to keep the data in A to E from being deleted if possible. So column G to column U.
 
Upvote 0

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
58,605
Office Version
  1. 365
Platform
  1. Windows
Thanks. Just noting that with the sample data provided, that would delete everything in columns G:U apart from the headings.

If you data is not huge you could try this (test with a copy of your data)

VBA Code:
Sub Del_Rows()
  Dim rDel As Range
  
  Application.ScreenUpdating = False
  With Range("G1:U" & Range("G" & Rows.Count).End(xlUp).Row)
    .AutoFilter Field:=7, Criteria1:=1
    Set rDel = .Offset(1).SpecialCells(xlVisible)
    .AutoFilter Field:=7
    .AutoFilter Field:=12, Criteria1:=0
    Set rDel = Union(rDel, .Offset(1).SpecialCells(xlVisible))
    .AutoFilter Field:=12
    rDel.Delete Shift:=xlUp
  End With
  Application.ScreenUpdating = True
End Sub

If you data is very large and the above takes too much time, post back with details of the size as a faster method is available (but the code is a bit longer to write :))
 
Upvote 0
Solution

cspengel

Board Regular
Joined
Oct 29, 2022
Messages
159
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
Thanks. Just noting that with the sample data provided, that would delete everything in columns G:U apart from the headings.

If you data is not huge you could try this (test with a copy of your data)

VBA Code:
Sub Del_Rows()
  Dim rDel As Range
 
  Application.ScreenUpdating = False
  With Range("G1:U" & Range("G" & Rows.Count).End(xlUp).Row)
    .AutoFilter Field:=7, Criteria1:=1
    Set rDel = .Offset(1).SpecialCells(xlVisible)
    .AutoFilter Field:=7
    .AutoFilter Field:=12, Criteria1:=0
    Set rDel = Union(rDel, .Offset(1).SpecialCells(xlVisible))
    .AutoFilter Field:=12
    rDel.Delete Shift:=xlUp
  End With
  Application.ScreenUpdating = True
End Sub

If you data is very large and the above takes too much time, post back with details of the size as a faster method is available (but the code is a bit longer to write :))
Appreciate the reply! I will try it. My data can contain over 400k rows. As for the data to the left of column G. I was trying to utilize a method of copying it to another sheet then moving it back to the sheet after the filter delete.
 
Upvote 0

cspengel

Board Regular
Joined
Oct 29, 2022
Messages
159
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
So after running your code, I still receive the issue I was referring to. My data for some reason becomes mismatched.

If you look at column N compared to O, they should match. column N is calculated during the macro at a earlier point. Column O is just a vlookup added for the sake of this post to show what the actual values are supposed to be. Rows 2-27 are incorrect. while the remaining are correct.

Showdown Slate1.xlsm
ABCDEFGHIJKLMNOP
2Dak PrescottDak PrescottDak PrescottDak PrescottDak PrescottDak PrescottCeeDee LambTony PollardJake FergusonChase McLaughlin24475900069.14569.635FALSE
3CeeDee LambCeeDee LambCeeDee LambCeeDee LambCeeDee LambDak PrescottCeeDee LambTony PollardKylen GransonMichael Gallup24706000068.49568.985FALSE
4Tony PollardTony PollardTony PollardTony PollardTony PollardDak PrescottCeeDee LambTony PollardKylen GransonChase McLaughlin24715950068.18568.675FALSE
5Jonathan TaylorJonathan TaylorJonathan TaylorJonathan TaylorJonathan TaylorDak PrescottCeeDee LambTony PollardJelani WoodsMichael Gallup24826000068.46568.955FALSE
6Matt RyanMatt RyanMatt RyanMatt RyanMatt RyanDak PrescottCeeDee LambTony PollardJelani WoodsChase McLaughlin24835950068.15568.645FALSE
7Ezekiel ElliottEzekiel ElliottEzekiel ElliottEzekiel ElliottEzekiel ElliottDak PrescottCeeDee LambTony PollardMo Alie-CoxMichael Gallup24946000067.33567.825FALSE
8Michael Pittman Jr.Michael Pittman Jr.Michael Pittman Jr.Michael Pittman Jr.Michael Pittman Jr.Dak PrescottCeeDee LambTony PollardMo Alie-CoxChase McLaughlin24955950067.02567.515FALSE
9Alec PierceDalton SchultzDalton SchultzDak PrescottCeeDee LambTony PollardKylen GransonBrett Maher26256000068.52569.585FALSE
10Indianapolis ColtsJake FergusonBrett MaherDak PrescottCeeDee LambTony PollardKylen GransonMichael Gallup26265950067.92568.985FALSE
11Deon JacksonPeyton HendershotMichael GallupDak PrescottCeeDee LambTony PollardKylen GransonChase McLaughlin26275900067.61568.675FALSE
12Jake FergusonKylen GransonChase McLaughlinDak PrescottCeeDee LambTony PollardJelani WoodsMichael Gallup26385950067.89568.955FALSE
13Peyton HendershotJelani WoodsDallas CowboysDak PrescottCeeDee LambTony PollardJelani WoodsChase McLaughlin26395900067.58568.645FALSE
14Mo Alie-CoxDak PrescottCeeDee LambTony PollardMo Alie-CoxBrett Maher26496000067.36568.425FALSE
15Dak PrescottCeeDee LambTony PollardMo Alie-CoxMichael Gallup26505950066.76567.825FALSE
16Dak PrescottCeeDee LambTony PollardMo Alie-CoxChase McLaughlin26515900066.45567.515FALSE
17Dak PrescottCeeDee LambTony PollardDalton SchultzChase McLaughlin29035900071.90575.475FALSE
18Dak PrescottCeeDee LambTony PollardKylen GransonDallas Cowboys29405900064.30567.875FALSE
19Dak PrescottCeeDee LambTony PollardJelani WoodsDallas Cowboys29525900064.27567.845FALSE
20Dak PrescottCeeDee LambTony PollardMo Alie-CoxDallas Cowboys29645900063.14566.715FALSE
21Dak PrescottCeeDee LambTony PollardJonathan TaylorDalton Schultz30086000070.80580.265FALSE
22Dak PrescottCeeDee LambTony PollardMatt RyanDalton Schultz30205950070.23579.695FALSE
23Dak PrescottCeeDee LambTony PollardMichael Pittman Jr.Brett Maher30456000067.53576.995FALSE
24Dak PrescottCeeDee LambTony PollardMichael Pittman Jr.Michael Gallup30465950066.93576.395FALSE
25Dak PrescottCeeDee LambTony PollardMichael Pittman Jr.Chase McLaughlin30475900066.62576.085FALSE
26Dak PrescottCeeDee LambTony PollardTony PollardChase McLaughlin31556000069.84579.655FALSE
27Dak PrescottCeeDee LambIndianapolis ColtsMatt RyanDalton Schultz31766000069.88569.885TRUE
28Dak PrescottCeeDee LambIndianapolis ColtsMichael Pittman Jr.Michael Gallup32026000066.58566.585TRUE
29Dak PrescottCeeDee LambIndianapolis ColtsMichael Pittman Jr.Chase McLaughlin32035950066.27566.275TRUE
30Dak PrescottCeeDee LambIndianapolis ColtsKylen GransonJonathan Taylor32445900063.65563.655TRUE
31Dak PrescottCeeDee LambIndianapolis ColtsJelani WoodsJonathan Taylor32565900063.62563.625TRUE
Worksheet
Cell Formulas
RangeFormula
O2:O31O2=VLOOKUP([@QB],Table2,3,0)*(1.5)+VLOOKUP([@FLEX],Table2,3,0)+VLOOKUP([@FLEX2],Table2,3,0)+VLOOKUP([@FLEX3],Table2,3,0)+VLOOKUP([@FLEX4],Table2,3,0)
P2:P31P2=[@[Σ Projection]]=[@Column1]


Now if you look at this one without the delete, everything matches and is in order. I dont understand why the delete throws it all off.

Showdown Slate1.xlsm
ABCDEFGHIJKLMNOP
1QBFLEXFLEXFLEXFLEXQBFLEXFLEX2FLEX3FLEX4ComboIDΣ SalaryΣ ProjectionColumn1Column2
2Dak PrescottDak PrescottDak PrescottDak PrescottDak PrescottDak PrescottCeeDee LambTony PollardJonathan TaylorMatt Ryan2225183.38583.385TRUE
3CeeDee LambCeeDee LambCeeDee LambCeeDee LambCeeDee LambDak PrescottCeeDee LambTony PollardJonathan TaylorEzekiel Elliott2226183.29583.295TRUE
4Tony PollardTony PollardTony PollardTony PollardTony PollardDak PrescottCeeDee LambTony PollardJonathan TaylorMichael Pittman Jr.2227180.87580.875TRUE
5Jonathan TaylorJonathan TaylorJonathan TaylorJonathan TaylorJonathan TaylorDak PrescottCeeDee LambTony PollardJonathan TaylorDalton Schultz2228180.26580.265TRUE
6Matt RyanMatt RyanMatt RyanMatt RyanMatt RyanDak PrescottCeeDee LambTony PollardJonathan TaylorBrett Maher2229180.07580.075TRUE
7Ezekiel ElliottEzekiel ElliottEzekiel ElliottEzekiel ElliottEzekiel ElliottDak PrescottCeeDee LambTony PollardJonathan TaylorMichael Gallup2230179.47579.475TRUE
8Michael Pittman Jr.Michael Pittman Jr.Michael Pittman Jr.Michael Pittman Jr.Michael Pittman Jr.Dak PrescottCeeDee LambTony PollardJonathan TaylorChase McLaughlin2231179.16579.165TRUE
9Alec PierceDalton SchultzDalton SchultzDak PrescottCeeDee LambTony PollardJonathan TaylorDallas Cowboys2232178.36578.365TRUE
10Indianapolis ColtsJake FergusonBrett MaherDak PrescottCeeDee LambTony PollardMatt RyanEzekiel Elliott2238182.72582.725TRUE
11Deon JacksonPeyton HendershotMichael GallupDak PrescottCeeDee LambTony PollardMatt RyanMichael Pittman Jr.2239180.30580.305TRUE
12Jake FergusonKylen GransonChase McLaughlinDak PrescottCeeDee LambTony PollardMatt RyanDalton Schultz2240179.69579.695TRUE
13Peyton HendershotJelani WoodsDallas CowboysDak PrescottCeeDee LambTony PollardMatt RyanBrett Maher2241179.50579.505TRUE
14Mo Alie-CoxDak PrescottCeeDee LambTony PollardMatt RyanMichael Gallup2242178.90578.905TRUE
15Dak PrescottCeeDee LambTony PollardMatt RyanChase McLaughlin2243178.59578.595TRUE
16Dak PrescottCeeDee LambTony PollardMatt RyanDallas Cowboys2244177.79577.795TRUE
17Dak PrescottCeeDee LambTony PollardEzekiel ElliottMichael Pittman Jr.2251180.21580.215TRUE
18Dak PrescottCeeDee LambTony PollardEzekiel ElliottDalton Schultz2252179.60579.605TRUE
19Dak PrescottCeeDee LambTony PollardEzekiel ElliottBrett Maher2253179.41579.415TRUE
20Dak PrescottCeeDee LambTony PollardEzekiel ElliottMichael Gallup2254178.81578.815TRUE
21Dak PrescottCeeDee LambTony PollardEzekiel ElliottChase McLaughlin2255178.50578.505TRUE
22Dak PrescottCeeDee LambTony PollardEzekiel ElliottDallas Cowboys2256177.70577.705TRUE
23Dak PrescottCeeDee LambTony PollardMichael Pittman Jr.Dalton Schultz2264177.18577.185TRUE
24Dak PrescottCeeDee LambTony PollardMichael Pittman Jr.Brett Maher2265176.99576.995TRUE
25Dak PrescottCeeDee LambTony PollardMichael Pittman Jr.Michael Gallup2266176.39576.395TRUE
26Dak PrescottCeeDee LambTony PollardMichael Pittman Jr.Chase McLaughlin2267176.08576.085TRUE
27Dak PrescottCeeDee LambTony PollardMichael Pittman Jr.Dallas Cowboys2268175.28575.285TRUE
28Dak PrescottCeeDee LambTony PollardDalton SchultzBrett Maher2277176.38576.385TRUE
29Dak PrescottCeeDee LambTony PollardDalton SchultzMichael Gallup22786000075.78575.785TRUE
30Dak PrescottCeeDee LambTony PollardDalton SchultzChase McLaughlin22795950075.47575.475TRUE
31Dak PrescottCeeDee LambTony PollardDalton SchultzDallas Cowboys2280174.67574.675TRUE
Worksheet
Cell Formulas
RangeFormula
O2:O31O2=VLOOKUP([@QB],Table2,3,0)*(1.5)+VLOOKUP([@FLEX],Table2,3,0)+VLOOKUP([@FLEX2],Table2,3,0)+VLOOKUP([@FLEX3],Table2,3,0)+VLOOKUP([@FLEX4],Table2,3,0)
P2:P31P2=[@[Σ Projection]]=[@Column1]
 
Upvote 0

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
58,605
Office Version
  1. 365
Platform
  1. Windows
You didn't tell or show us ..
  • That G:U was a formal Excel table (ListObject)
  • That there was already a macro that this was to combined with
  • That there were formula anywhere except column S
  • That apparently there is another table (Table2) somewhere
Perhaps you could start again (still in this thread) and give us a new small but representative set of sample data together with the full story and any existing code that is relevant to this task?
 
Upvote 0

cspengel

Board Regular
Joined
Oct 29, 2022
Messages
159
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
Apologies.

G:U is not a formal excel table. (ListObject). I only turned it into one when creating the xl2bb.
The vlookup in column O is not part of my data.
Column P is not part of the data either.

They were added to show what column N should show as it is calculated during the macro.

I have 3 sheets. The first sheet is what is posted aboved called "Worksheet",
the second sheet is called "Salary". Vstack is used to pull Unique names from U:E.
the 3rd is called game data. This is table2 and is just used to pull data to the salary sheet.

this is the salary sheet. It is not a table.

Showdown Slate1.xlsm
ABCDE
1NameNameSalaryProjectionTeam
2Alec PierceAlec Pierce75004.14IND
3Brett MaherBrett Maher95009.23DAL
4CeeDee LambCeeDee Lamb1400014.9DAL
5Chase McLaughlinChase McLaughlin85008.32IND
6Dak PrescottDak Prescott1600019.49DAL
7Dallas CowboysDallas Cowboys100007.52DAL
8Dalton SchultzDalton Schultz75009.42DAL
9Deon JacksonDeon Jackson65003.72IND
10Ezekiel ElliottEzekiel Elliott1200012.45DAL
11Indianapolis ColtsIndianapolis Colts80003.79IND
12Jake FergusonJake Ferguson55003.58DAL
13Jelani WoodsJelani Woods60002.59IND
14Jonathan TaylorJonathan Taylor1500013.11IND
15Kylen GransonKylen Granson60002.62IND
16Matt RyanMatt Ryan1450012.54IND
17Michael GallupMichael Gallup90008.63DAL
18Michael Pittman Jr.Michael Pittman Jr.1300010.03IND
19Mo Alie-CoxMo Alie-Cox60001.46IND
20Peyton HendershotPeyton Hendershot50002.7DAL
21Tony PollardTony Pollard1350013.6DAL
220
Salary
Cell Formulas
RangeFormula
A2:A22A2=SORT(UNIQUE(VSTACK(Worksheet!A2:A15,Worksheet!B2:B16,Worksheet!C2:C16,Worksheet!D2:D16,Worksheet!E2:E15)))
B2:B21B2=A2
C2:C21C2=VLOOKUP(B2,Table2,2,0)
D2:D21D2=VLOOKUP(B2,Table2,3,0)
E2:E21E2=VLOOKUP(B2,Table2,4,0)
Dynamic array formulas.


this is the Game Data table.
Showdown Slate1.xlsm
ABCDE
2Dalton Schultz75009.42DAL4.3
3Nick Foles50000IND0
4Parris Campbell110006.71IND6.5
5Nick Ralston50000DAL0
6Ashton Dulin55000.66IND7.9
7Sean McKeon50000DAL0
8Brandon Smith50000DAL0
9Phillip Lindsay55000IND0
10Jeremy Sprinkle50000DAL0
11Qadree Ollison50000DAL0
12Ian Bunting50000DAL0
13Aaron Shampklin50000DAL0
14Will Grier50000DAL0
15Dak Prescott1600019.49DAL17.8
16Jake Funk50000IND0
17Drew Ogletree50000IND0
18John Hurst50000IND0
19Lirim Hajrullahu50000DAL0
20Simi Fehoko50000DAL0
21Ben DiNucci50000DAL0
22Michael Gallup90008.63DAL16.3
23D'vonte Price50000IND0
24D.J. Montgomery50000IND0
25Noah Brown70004.86DAL2.5
26Jordan Glasgow50000IND0
27Tony Pollard1350013.6DAL23.6
28CJ Verdell50000IND0
29Jelani Woods60002.59IND3.8
30Jake Ferguson55003.58DAL0.9
31Nakia Griffin-Stewart50000IND0
32Rico Dowdle50000DAL0
33Matt Ryan1450012.54IND12.12
34Ethan Fernea50000IND0
35Jack Coan50000IND0
36Samson Nacua50000IND0
37Ezekiel Elliott1200012.45DAL16.6
38T.J. Vasher50000DAL0
39Jared Scott50000IND0
Game Data


During the macro the names you see listed in G:K are copied to a few helper columns and replaced with their designated projection on the salary sheet. the projections are then totaled and sum'd into the N Column. After all of that. The helper columns are deleted and you are left with the information above. None of that information should affect the deletion. It is all calculated and helper columns are removed before what I am trying to accomplish now. :(
 
Upvote 0

cspengel

Board Regular
Joined
Oct 29, 2022
Messages
159
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
Here is the main worksheet from post #1 with all the helper columns:

Showdown Slate1.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJ
1QBFLEXFLEXFLEXFLEXQBFLEXFLEXFLEXFLEXComboIDQBFLEXFLEXFLEXFLEXQBFLEXFLEXFLEXFLEXQBFLEXFLEXFLEXFLEXΣ SalaryΣ ProjectionΣ ValueΣ StackΣ Stack POSΣ CommasΣ FilterΣ Player1Σ Player2
2Dak PrescottDak PrescottDak PrescottDak PrescottDak PrescottDak PrescottCeeDee LambTony PollardJonathan TaylorMatt Ryan2225160001400013500150001450019.49013.613.1112.54DALDALDALINDIND183.38583385DALQB,FLEX,FLEX3
3CeeDee LambCeeDee LambCeeDee LambCeeDee LambCeeDee LambDak PrescottCeeDee LambTony PollardJonathan TaylorEzekiel Elliott2226160001400013500150001200019.49013.613.1112.45DALDALDALINDDAL183.29583295DALQB,FLEX,FLEX,FLEX0
4Tony PollardTony PollardTony PollardTony PollardTony PollardDak PrescottCeeDee LambTony PollardJonathan TaylorMichael Pittman Jr.2227160001400013500150001300019.49013.613.1110.03DALDALDALINDIND180.87580875DALQB,FLEX,FLEX3
5Jonathan TaylorJonathan TaylorJonathan TaylorJonathan TaylorJonathan TaylorDak PrescottCeeDee LambTony PollardJonathan TaylorDalton Schultz222816000140001350015000750019.49013.613.119.42DALDALDALINDDAL180.26580265DALQB,FLEX,FLEX,FLEX0
6Matt RyanMatt RyanMatt RyanMatt RyanMatt RyanDak PrescottCeeDee LambTony PollardJonathan TaylorBrett Maher222916000140001350015000950019.49013.613.119.23DALDALDALINDDAL180.07580075DALQB,FLEX,FLEX,FLEX0
7Ezekiel ElliottEzekiel ElliottEzekiel ElliottEzekiel ElliottEzekiel ElliottDak PrescottCeeDee LambTony PollardJonathan TaylorMichael Gallup223016000140001350015000900019.49013.613.118.63DALDALDALINDDAL179.47579475DALQB,FLEX,FLEX,FLEX0
8Michael Pittman Jr.Michael Pittman Jr.Michael Pittman Jr.Michael Pittman Jr.Michael Pittman Jr.Dak PrescottCeeDee LambTony PollardJonathan TaylorChase McLaughlin223116000140001350015000850019.49013.613.118.32DALDALDALINDIND179.16579165DALQB,FLEX,FLEX3
9Alec PierceDalton SchultzDalton SchultzDak PrescottCeeDee LambTony PollardJonathan TaylorDallas Cowboys2232160001400013500150001000019.49013.613.117.52DALDALDALINDDAL178.36578365DALQB,FLEX,FLEX,FLEX0
10Indianapolis ColtsJake FergusonBrett MaherDak PrescottCeeDee LambTony PollardMatt RyanEzekiel Elliott2238160001400013500145001200019.49013.612.5412.45DALDALDALINDDAL182.72582725DALQB,FLEX,FLEX,FLEX0
11Deon JacksonPeyton HendershotMichael GallupDak PrescottCeeDee LambTony PollardMatt RyanMichael Pittman Jr.2239160001400013500145001300019.49013.612.5410.03DALDALDALINDIND180.30580305DALQB,FLEX,FLEX3
12Jake FergusonKylen GransonChase McLaughlinDak PrescottCeeDee LambTony PollardMatt RyanDalton Schultz224016000140001350014500750019.49013.612.549.42DALDALDALINDDAL179.69579695DALQB,FLEX,FLEX,FLEX0
13Peyton HendershotJelani WoodsDallas CowboysDak PrescottCeeDee LambTony PollardMatt RyanBrett Maher224116000140001350014500950019.49013.612.549.23DALDALDALINDDAL179.50579505DALQB,FLEX,FLEX,FLEX0
14Mo Alie-CoxDak PrescottCeeDee LambTony PollardMatt RyanMichael Gallup224216000140001350014500900019.49013.612.548.63DALDALDALINDDAL178.90578905DALQB,FLEX,FLEX,FLEX0
15Dak PrescottCeeDee LambTony PollardMatt RyanChase McLaughlin224316000140001350014500850019.49013.612.548.32DALDALDALINDIND178.59578595DALQB,FLEX,FLEX3
16Dak PrescottCeeDee LambTony PollardMatt RyanDallas Cowboys2244160001400013500145001000019.49013.612.547.52DALDALDALINDDAL177.79577795DALQB,FLEX,FLEX,FLEX0
17Dak PrescottCeeDee LambTony PollardEzekiel ElliottMichael Pittman Jr.2251160001400013500120001300019.49013.612.4510.03DALDALDALDALIND180.21580215DALQB,FLEX,FLEX,FLEX0
18Dak PrescottCeeDee LambTony PollardEzekiel ElliottDalton Schultz225216000140001350012000750019.49013.612.459.42DALDALDALDALDAL179.60579605DALQB,FLEX,FLEX,FLEX,FLEX0
Worksheet
Cell Formulas
RangeFormula
S2:S18S2=IF($U$2="",COUNTIF(J2:K2,$T$2),COUNTIF(J2:K2,$T$2)*COUNTIF(J2:K2,$U$2))


The helper columns (M:AA) are deleted before the macro ends.

After the helper columns are deleted, that is when I want to delete what is described in post#1. Column N (which is projection column after helper columns deleted) is calculated by adding up Helper columns R:U.

the countif formula is not part of the helper columns. It only appears so because I turned off the deleting of the helper columns for this post.
 
Upvote 0

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
58,605
Office Version
  1. 365
Platform
  1. Windows
The first sheet is what is posted aboved called "Worksheet",
You have posted 'Worksheet' three times above. Which one should I be looking at to start with? Or should I be using the one from post #9?

During the macro ..
We still have not seen the macro so cannot see what it is doing or test with it.
 
Upvote 0

Forum statistics

Threads
1,187,001
Messages
5,961,055
Members
438,516
Latest member
Fintrics

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