Delete based on cell value

[cspengel]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	QB	FLEX	FLEX	FLEX	FLEX		QB	FLEX	FLEX	FLEX	FLEX	ComboID	Σ Salary	Σ Projection	Σ Value	Σ Stack	Σ Stack POS	Σ Commas	Σ Filter	Σ Player1	Σ Player2
2	Dak Prescott	Dak Prescott	Dak Prescott	Dak Prescott	Dak Prescott		Dak Prescott	CeeDee Lamb	Tony Pollard	Jonathan Taylor	Matt Ryan	2225	1	83.385	83385	DAL	QB,FLEX,FLEX	3	0
3	CeeDee Lamb	CeeDee Lamb	CeeDee Lamb	CeeDee Lamb	CeeDee Lamb		Dak Prescott	CeeDee Lamb	Tony Pollard	Jonathan Taylor	Ezekiel Elliott	2226	1	83.295	83295	DAL	QB,FLEX,FLEX,FLEX	0	0
4	Tony Pollard	Tony Pollard	Tony Pollard	Tony Pollard	Tony Pollard		Dak Prescott	CeeDee Lamb	Tony Pollard	Dalton Schultz	Brett Maher	2277	1	76.385	76385	DAL	QB,FLEX,FLEX,FLEX,FLEX	0	0
5	Jonathan Taylor	Jonathan Taylor	Jonathan Taylor	Jonathan Taylor	Jonathan Taylor		Dak Prescott	CeeDee Lamb	Tony Pollard	Dalton Schultz	Michael Gallup	2278	60000	75.785	1.263083333	DAL	QB,FLEX,FLEX,FLEX,FLEX	0	0
6	Matt Ryan	Matt Ryan	Matt Ryan	Matt Ryan	Matt Ryan		Dak Prescott	CeeDee Lamb	Tony Pollard	Dalton Schultz	Chase McLaughlin	2279	59500	75.475	1.268487395	DAL	QB,FLEX,FLEX,FLEX	0	0
7	Ezekiel Elliott	Ezekiel Elliott	Ezekiel Elliott	Ezekiel Elliott	Ezekiel Elliott		Dak Prescott	CeeDee Lamb	Tony Pollard	Dalton Schultz	Dallas Cowboys	2280	1	74.675	74675	DAL	QB,FLEX,FLEX,FLEX,FLEX	0	0
8	Michael Pittman Jr.	Michael Pittman Jr.	Michael Pittman Jr.	Michael Pittman Jr.	Michael Pittman Jr.		Dak Prescott	CeeDee Lamb	Tony Pollard	Jake Ferguson	Chase McLaughlin	2291	1	69.635	69635	DAL	QB,FLEX,FLEX,FLEX	0	0
9			Alec Pierce	Dalton Schultz	Dalton Schultz		Dak Prescott	CeeDee Lamb	Tony Pollard	Jake Ferguson	Dallas Cowboys	2292	59000	68.835	1.166694915	DAL	QB,FLEX,FLEX,FLEX,FLEX	0	0
10			Indianapolis Colts	Jake Ferguson	Brett Maher		Dak Prescott	CeeDee Lamb	Tony Pollard	Peyton Hendershot	Jonathan Taylor	2296	1	73.545	73545	DAL	QB,FLEX,FLEX,FLEX	0	0
11			Deon Jackson	Peyton Hendershot	Michael Gallup		Dak Prescott	CeeDee Lamb	Tony Pollard	Peyton Hendershot	Matt Ryan	2297	1	72.975	72975	DAL	QB,FLEX,FLEX,FLEX	0	0
12			Jake Ferguson	Kylen Granson	Chase McLaughlin		Dak Prescott	CeeDee Lamb	Tony Pollard	Kylen Granson	Dalton Schultz	2312	1	69.775	69775	DAL	QB,FLEX,FLEX,FLEX	0	0
13			Peyton Hendershot	Jelani Woods	Dallas Cowboys		Dak Prescott	CeeDee Lamb	Tony Pollard	Kylen Granson	Brett Maher	2313	59000	69.585	1.17940678	DAL	QB,FLEX,FLEX,FLEX	0	0
14				Mo Alie-Cox			Dak Prescott	CeeDee Lamb	Tony Pollard	Kylen Granson	Michael Gallup	2314	1	68.985	68985	DAL	QB,FLEX,FLEX,FLEX	0	0
15							Dak Prescott	CeeDee Lamb	Tony Pollard	Kylen Granson	Chase McLaughlin	2315	1	68.675	68675	DAL	QB,FLEX,FLEX	3	0
16							Dak Prescott	CeeDee Lamb	Tony Pollard	Kylen Granson	Dallas Cowboys	2316	59500	67.875	1.140756303	DAL	QB,FLEX,FLEX,FLEX	0	0
Worksheet

Cell Formulas
Range	Formula
S2:S16	S2	=IF($U$2="",COUNTIF(G2:K2,$T$2),COUNTIF(G2:K2,$T$2)*COUNTIF(G2:K2,$U$2))

 

[Peter_SSs]

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?

 

[cspengel]

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.

 

[Peter_SSs]

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)

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 )

 

[cspengel]

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)

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.

 

[cspengel]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
2	Dak Prescott	Dak Prescott	Dak Prescott	Dak Prescott	Dak Prescott		Dak Prescott	CeeDee Lamb	Tony Pollard	Jake Ferguson	Chase McLaughlin	2447	59000	69.145	69.635	FALSE
3	CeeDee Lamb	CeeDee Lamb	CeeDee Lamb	CeeDee Lamb	CeeDee Lamb		Dak Prescott	CeeDee Lamb	Tony Pollard	Kylen Granson	Michael Gallup	2470	60000	68.495	68.985	FALSE
4	Tony Pollard	Tony Pollard	Tony Pollard	Tony Pollard	Tony Pollard		Dak Prescott	CeeDee Lamb	Tony Pollard	Kylen Granson	Chase McLaughlin	2471	59500	68.185	68.675	FALSE
5	Jonathan Taylor	Jonathan Taylor	Jonathan Taylor	Jonathan Taylor	Jonathan Taylor		Dak Prescott	CeeDee Lamb	Tony Pollard	Jelani Woods	Michael Gallup	2482	60000	68.465	68.955	FALSE
6	Matt Ryan	Matt Ryan	Matt Ryan	Matt Ryan	Matt Ryan		Dak Prescott	CeeDee Lamb	Tony Pollard	Jelani Woods	Chase McLaughlin	2483	59500	68.155	68.645	FALSE
7	Ezekiel Elliott	Ezekiel Elliott	Ezekiel Elliott	Ezekiel Elliott	Ezekiel Elliott		Dak Prescott	CeeDee Lamb	Tony Pollard	Mo Alie-Cox	Michael Gallup	2494	60000	67.335	67.825	FALSE
8	Michael Pittman Jr.	Michael Pittman Jr.	Michael Pittman Jr.	Michael Pittman Jr.	Michael Pittman Jr.		Dak Prescott	CeeDee Lamb	Tony Pollard	Mo Alie-Cox	Chase McLaughlin	2495	59500	67.025	67.515	FALSE
9			Alec Pierce	Dalton Schultz	Dalton Schultz		Dak Prescott	CeeDee Lamb	Tony Pollard	Kylen Granson	Brett Maher	2625	60000	68.525	69.585	FALSE
10			Indianapolis Colts	Jake Ferguson	Brett Maher		Dak Prescott	CeeDee Lamb	Tony Pollard	Kylen Granson	Michael Gallup	2626	59500	67.925	68.985	FALSE
11			Deon Jackson	Peyton Hendershot	Michael Gallup		Dak Prescott	CeeDee Lamb	Tony Pollard	Kylen Granson	Chase McLaughlin	2627	59000	67.615	68.675	FALSE
12			Jake Ferguson	Kylen Granson	Chase McLaughlin		Dak Prescott	CeeDee Lamb	Tony Pollard	Jelani Woods	Michael Gallup	2638	59500	67.895	68.955	FALSE
13			Peyton Hendershot	Jelani Woods	Dallas Cowboys		Dak Prescott	CeeDee Lamb	Tony Pollard	Jelani Woods	Chase McLaughlin	2639	59000	67.585	68.645	FALSE
14				Mo Alie-Cox			Dak Prescott	CeeDee Lamb	Tony Pollard	Mo Alie-Cox	Brett Maher	2649	60000	67.365	68.425	FALSE
15							Dak Prescott	CeeDee Lamb	Tony Pollard	Mo Alie-Cox	Michael Gallup	2650	59500	66.765	67.825	FALSE
16							Dak Prescott	CeeDee Lamb	Tony Pollard	Mo Alie-Cox	Chase McLaughlin	2651	59000	66.455	67.515	FALSE
17							Dak Prescott	CeeDee Lamb	Tony Pollard	Dalton Schultz	Chase McLaughlin	2903	59000	71.905	75.475	FALSE
18							Dak Prescott	CeeDee Lamb	Tony Pollard	Kylen Granson	Dallas Cowboys	2940	59000	64.305	67.875	FALSE
19							Dak Prescott	CeeDee Lamb	Tony Pollard	Jelani Woods	Dallas Cowboys	2952	59000	64.275	67.845	FALSE
20							Dak Prescott	CeeDee Lamb	Tony Pollard	Mo Alie-Cox	Dallas Cowboys	2964	59000	63.145	66.715	FALSE
21							Dak Prescott	CeeDee Lamb	Tony Pollard	Jonathan Taylor	Dalton Schultz	3008	60000	70.805	80.265	FALSE
22							Dak Prescott	CeeDee Lamb	Tony Pollard	Matt Ryan	Dalton Schultz	3020	59500	70.235	79.695	FALSE
23							Dak Prescott	CeeDee Lamb	Tony Pollard	Michael Pittman Jr.	Brett Maher	3045	60000	67.535	76.995	FALSE
24							Dak Prescott	CeeDee Lamb	Tony Pollard	Michael Pittman Jr.	Michael Gallup	3046	59500	66.935	76.395	FALSE
25							Dak Prescott	CeeDee Lamb	Tony Pollard	Michael Pittman Jr.	Chase McLaughlin	3047	59000	66.625	76.085	FALSE
26							Dak Prescott	CeeDee Lamb	Tony Pollard	Tony Pollard	Chase McLaughlin	3155	60000	69.845	79.655	FALSE
27							Dak Prescott	CeeDee Lamb	Indianapolis Colts	Matt Ryan	Dalton Schultz	3176	60000	69.885	69.885	TRUE
28							Dak Prescott	CeeDee Lamb	Indianapolis Colts	Michael Pittman Jr.	Michael Gallup	3202	60000	66.585	66.585	TRUE
29							Dak Prescott	CeeDee Lamb	Indianapolis Colts	Michael Pittman Jr.	Chase McLaughlin	3203	59500	66.275	66.275	TRUE
30							Dak Prescott	CeeDee Lamb	Indianapolis Colts	Kylen Granson	Jonathan Taylor	3244	59000	63.655	63.655	TRUE
31							Dak Prescott	CeeDee Lamb	Indianapolis Colts	Jelani Woods	Jonathan Taylor	3256	59000	63.625	63.625	TRUE
Worksheet

Cell Formulas
Range	Formula
O2:O31	O2	=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:P31	P2	=[@[Σ 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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	QB	FLEX	FLEX	FLEX	FLEX		QB	FLEX	FLEX2	FLEX3	FLEX4	ComboID	Σ Salary	Σ Projection	Column1	Column2
2	Dak Prescott	Dak Prescott	Dak Prescott	Dak Prescott	Dak Prescott		Dak Prescott	CeeDee Lamb	Tony Pollard	Jonathan Taylor	Matt Ryan	2225	1	83.385	83.385	TRUE
3	CeeDee Lamb	CeeDee Lamb	CeeDee Lamb	CeeDee Lamb	CeeDee Lamb		Dak Prescott	CeeDee Lamb	Tony Pollard	Jonathan Taylor	Ezekiel Elliott	2226	1	83.295	83.295	TRUE
4	Tony Pollard	Tony Pollard	Tony Pollard	Tony Pollard	Tony Pollard		Dak Prescott	CeeDee Lamb	Tony Pollard	Jonathan Taylor	Michael Pittman Jr.	2227	1	80.875	80.875	TRUE
5	Jonathan Taylor	Jonathan Taylor	Jonathan Taylor	Jonathan Taylor	Jonathan Taylor		Dak Prescott	CeeDee Lamb	Tony Pollard	Jonathan Taylor	Dalton Schultz	2228	1	80.265	80.265	TRUE
6	Matt Ryan	Matt Ryan	Matt Ryan	Matt Ryan	Matt Ryan		Dak Prescott	CeeDee Lamb	Tony Pollard	Jonathan Taylor	Brett Maher	2229	1	80.075	80.075	TRUE
7	Ezekiel Elliott	Ezekiel Elliott	Ezekiel Elliott	Ezekiel Elliott	Ezekiel Elliott		Dak Prescott	CeeDee Lamb	Tony Pollard	Jonathan Taylor	Michael Gallup	2230	1	79.475	79.475	TRUE
8	Michael Pittman Jr.	Michael Pittman Jr.	Michael Pittman Jr.	Michael Pittman Jr.	Michael Pittman Jr.		Dak Prescott	CeeDee Lamb	Tony Pollard	Jonathan Taylor	Chase McLaughlin	2231	1	79.165	79.165	TRUE
9			Alec Pierce	Dalton Schultz	Dalton Schultz		Dak Prescott	CeeDee Lamb	Tony Pollard	Jonathan Taylor	Dallas Cowboys	2232	1	78.365	78.365	TRUE
10			Indianapolis Colts	Jake Ferguson	Brett Maher		Dak Prescott	CeeDee Lamb	Tony Pollard	Matt Ryan	Ezekiel Elliott	2238	1	82.725	82.725	TRUE
11			Deon Jackson	Peyton Hendershot	Michael Gallup		Dak Prescott	CeeDee Lamb	Tony Pollard	Matt Ryan	Michael Pittman Jr.	2239	1	80.305	80.305	TRUE
12			Jake Ferguson	Kylen Granson	Chase McLaughlin		Dak Prescott	CeeDee Lamb	Tony Pollard	Matt Ryan	Dalton Schultz	2240	1	79.695	79.695	TRUE
13			Peyton Hendershot	Jelani Woods	Dallas Cowboys		Dak Prescott	CeeDee Lamb	Tony Pollard	Matt Ryan	Brett Maher	2241	1	79.505	79.505	TRUE
14				Mo Alie-Cox			Dak Prescott	CeeDee Lamb	Tony Pollard	Matt Ryan	Michael Gallup	2242	1	78.905	78.905	TRUE
15							Dak Prescott	CeeDee Lamb	Tony Pollard	Matt Ryan	Chase McLaughlin	2243	1	78.595	78.595	TRUE
16							Dak Prescott	CeeDee Lamb	Tony Pollard	Matt Ryan	Dallas Cowboys	2244	1	77.795	77.795	TRUE
17							Dak Prescott	CeeDee Lamb	Tony Pollard	Ezekiel Elliott	Michael Pittman Jr.	2251	1	80.215	80.215	TRUE
18							Dak Prescott	CeeDee Lamb	Tony Pollard	Ezekiel Elliott	Dalton Schultz	2252	1	79.605	79.605	TRUE
19							Dak Prescott	CeeDee Lamb	Tony Pollard	Ezekiel Elliott	Brett Maher	2253	1	79.415	79.415	TRUE
20							Dak Prescott	CeeDee Lamb	Tony Pollard	Ezekiel Elliott	Michael Gallup	2254	1	78.815	78.815	TRUE
21							Dak Prescott	CeeDee Lamb	Tony Pollard	Ezekiel Elliott	Chase McLaughlin	2255	1	78.505	78.505	TRUE
22							Dak Prescott	CeeDee Lamb	Tony Pollard	Ezekiel Elliott	Dallas Cowboys	2256	1	77.705	77.705	TRUE
23							Dak Prescott	CeeDee Lamb	Tony Pollard	Michael Pittman Jr.	Dalton Schultz	2264	1	77.185	77.185	TRUE
24							Dak Prescott	CeeDee Lamb	Tony Pollard	Michael Pittman Jr.	Brett Maher	2265	1	76.995	76.995	TRUE
25							Dak Prescott	CeeDee Lamb	Tony Pollard	Michael Pittman Jr.	Michael Gallup	2266	1	76.395	76.395	TRUE
26							Dak Prescott	CeeDee Lamb	Tony Pollard	Michael Pittman Jr.	Chase McLaughlin	2267	1	76.085	76.085	TRUE
27							Dak Prescott	CeeDee Lamb	Tony Pollard	Michael Pittman Jr.	Dallas Cowboys	2268	1	75.285	75.285	TRUE
28							Dak Prescott	CeeDee Lamb	Tony Pollard	Dalton Schultz	Brett Maher	2277	1	76.385	76.385	TRUE
29							Dak Prescott	CeeDee Lamb	Tony Pollard	Dalton Schultz	Michael Gallup	2278	60000	75.785	75.785	TRUE
30							Dak Prescott	CeeDee Lamb	Tony Pollard	Dalton Schultz	Chase McLaughlin	2279	59500	75.475	75.475	TRUE
31							Dak Prescott	CeeDee Lamb	Tony Pollard	Dalton Schultz	Dallas Cowboys	2280	1	74.675	74.675	TRUE
Worksheet

Cell Formulas
Range	Formula
O2:O31	O2	=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:P31	P2	=[@[Σ Projection]]=[@Column1]

 

[Peter_SSs]

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?

 

[cspengel]

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
	A	B	C	D	E
1	Name	Name	Salary	Projection	Team
2	Alec Pierce	Alec Pierce	7500	4.14	IND
3	Brett Maher	Brett Maher	9500	9.23	DAL
4	CeeDee Lamb	CeeDee Lamb	14000	14.9	DAL
5	Chase McLaughlin	Chase McLaughlin	8500	8.32	IND
6	Dak Prescott	Dak Prescott	16000	19.49	DAL
7	Dallas Cowboys	Dallas Cowboys	10000	7.52	DAL
8	Dalton Schultz	Dalton Schultz	7500	9.42	DAL
9	Deon Jackson	Deon Jackson	6500	3.72	IND
10	Ezekiel Elliott	Ezekiel Elliott	12000	12.45	DAL
11	Indianapolis Colts	Indianapolis Colts	8000	3.79	IND
12	Jake Ferguson	Jake Ferguson	5500	3.58	DAL
13	Jelani Woods	Jelani Woods	6000	2.59	IND
14	Jonathan Taylor	Jonathan Taylor	15000	13.11	IND
15	Kylen Granson	Kylen Granson	6000	2.62	IND
16	Matt Ryan	Matt Ryan	14500	12.54	IND
17	Michael Gallup	Michael Gallup	9000	8.63	DAL
18	Michael Pittman Jr.	Michael Pittman Jr.	13000	10.03	IND
19	Mo Alie-Cox	Mo Alie-Cox	6000	1.46	IND
20	Peyton Hendershot	Peyton Hendershot	5000	2.7	DAL
21	Tony Pollard	Tony Pollard	13500	13.6	DAL
22	0
Salary

Cell Formulas
Range	Formula
A2:A22	A2	=SORT(UNIQUE(VSTACK(Worksheet!A2:A15,Worksheet!B2:B16,Worksheet!C2:C16,Worksheet!D2:D16,Worksheet!E2:E15)))
B2:B21	B2	=A2
C2:C21	C2	=VLOOKUP(B2,Table2,2,0)
D2:D21	D2	=VLOOKUP(B2,Table2,3,0)
E2:E21	E2	=VLOOKUP(B2,Table2,4,0)
Dynamic array formulas.

this is the Game Data table.

Showdown Slate1.xlsm
	A	B	C	D	E
2	Dalton Schultz	7500	9.42	DAL	4.3
3	Nick Foles	5000	0	IND	0
4	Parris Campbell	11000	6.71	IND	6.5
5	Nick Ralston	5000	0	DAL	0
6	Ashton Dulin	5500	0.66	IND	7.9
7	Sean McKeon	5000	0	DAL	0
8	Brandon Smith	5000	0	DAL	0
9	Phillip Lindsay	5500	0	IND	0
10	Jeremy Sprinkle	5000	0	DAL	0
11	Qadree Ollison	5000	0	DAL	0
12	Ian Bunting	5000	0	DAL	0
13	Aaron Shampklin	5000	0	DAL	0
14	Will Grier	5000	0	DAL	0
15	Dak Prescott	16000	19.49	DAL	17.8
16	Jake Funk	5000	0	IND	0
17	Drew Ogletree	5000	0	IND	0
18	John Hurst	5000	0	IND	0
19	Lirim Hajrullahu	5000	0	DAL	0
20	Simi Fehoko	5000	0	DAL	0
21	Ben DiNucci	5000	0	DAL	0
22	Michael Gallup	9000	8.63	DAL	16.3
23	D'vonte Price	5000	0	IND	0
24	D.J. Montgomery	5000	0	IND	0
25	Noah Brown	7000	4.86	DAL	2.5
26	Jordan Glasgow	5000	0	IND	0
27	Tony Pollard	13500	13.6	DAL	23.6
28	CJ Verdell	5000	0	IND	0
29	Jelani Woods	6000	2.59	IND	3.8
30	Jake Ferguson	5500	3.58	DAL	0.9
31	Nakia Griffin-Stewart	5000	0	IND	0
32	Rico Dowdle	5000	0	DAL	0
33	Matt Ryan	14500	12.54	IND	12.12
34	Ethan Fernea	5000	0	IND	0
35	Jack Coan	5000	0	IND	0
36	Samson Nacua	5000	0	IND	0
37	Ezekiel Elliott	12000	12.45	DAL	16.6
38	T.J. Vasher	5000	0	DAL	0
39	Jared Scott	5000	0	IND	0
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.

 

[cspengel]

Here is the main worksheet from post #1 with all the helper columns:

Showdown Slate1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ
1	QB	FLEX	FLEX	FLEX	FLEX		QB	FLEX	FLEX	FLEX	FLEX	ComboID	QB	FLEX	FLEX	FLEX	FLEX	QB	FLEX	FLEX	FLEX	FLEX	QB	FLEX	FLEX	FLEX	FLEX	Σ Salary	Σ Projection	Σ Value	Σ Stack	Σ Stack POS	Σ Commas	Σ Filter	Σ Player1	Σ Player2
2	Dak Prescott	Dak Prescott	Dak Prescott	Dak Prescott	Dak Prescott		Dak Prescott	CeeDee Lamb	Tony Pollard	Jonathan Taylor	Matt Ryan	2225	16000	14000	13500	15000	14500	19.49	0	13.6	13.11	12.54	DAL	DAL	DAL	IND	IND	1	83.385	83385	DAL	QB,FLEX,FLEX	3
3	CeeDee Lamb	CeeDee Lamb	CeeDee Lamb	CeeDee Lamb	CeeDee Lamb		Dak Prescott	CeeDee Lamb	Tony Pollard	Jonathan Taylor	Ezekiel Elliott	2226	16000	14000	13500	15000	12000	19.49	0	13.6	13.11	12.45	DAL	DAL	DAL	IND	DAL	1	83.295	83295	DAL	QB,FLEX,FLEX,FLEX	0
4	Tony Pollard	Tony Pollard	Tony Pollard	Tony Pollard	Tony Pollard		Dak Prescott	CeeDee Lamb	Tony Pollard	Jonathan Taylor	Michael Pittman Jr.	2227	16000	14000	13500	15000	13000	19.49	0	13.6	13.11	10.03	DAL	DAL	DAL	IND	IND	1	80.875	80875	DAL	QB,FLEX,FLEX	3
5	Jonathan Taylor	Jonathan Taylor	Jonathan Taylor	Jonathan Taylor	Jonathan Taylor		Dak Prescott	CeeDee Lamb	Tony Pollard	Jonathan Taylor	Dalton Schultz	2228	16000	14000	13500	15000	7500	19.49	0	13.6	13.11	9.42	DAL	DAL	DAL	IND	DAL	1	80.265	80265	DAL	QB,FLEX,FLEX,FLEX	0
6	Matt Ryan	Matt Ryan	Matt Ryan	Matt Ryan	Matt Ryan		Dak Prescott	CeeDee Lamb	Tony Pollard	Jonathan Taylor	Brett Maher	2229	16000	14000	13500	15000	9500	19.49	0	13.6	13.11	9.23	DAL	DAL	DAL	IND	DAL	1	80.075	80075	DAL	QB,FLEX,FLEX,FLEX	0
7	Ezekiel Elliott	Ezekiel Elliott	Ezekiel Elliott	Ezekiel Elliott	Ezekiel Elliott		Dak Prescott	CeeDee Lamb	Tony Pollard	Jonathan Taylor	Michael Gallup	2230	16000	14000	13500	15000	9000	19.49	0	13.6	13.11	8.63	DAL	DAL	DAL	IND	DAL	1	79.475	79475	DAL	QB,FLEX,FLEX,FLEX	0
8	Michael Pittman Jr.	Michael Pittman Jr.	Michael Pittman Jr.	Michael Pittman Jr.	Michael Pittman Jr.		Dak Prescott	CeeDee Lamb	Tony Pollard	Jonathan Taylor	Chase McLaughlin	2231	16000	14000	13500	15000	8500	19.49	0	13.6	13.11	8.32	DAL	DAL	DAL	IND	IND	1	79.165	79165	DAL	QB,FLEX,FLEX	3
9			Alec Pierce	Dalton Schultz	Dalton Schultz		Dak Prescott	CeeDee Lamb	Tony Pollard	Jonathan Taylor	Dallas Cowboys	2232	16000	14000	13500	15000	10000	19.49	0	13.6	13.11	7.52	DAL	DAL	DAL	IND	DAL	1	78.365	78365	DAL	QB,FLEX,FLEX,FLEX	0
10			Indianapolis Colts	Jake Ferguson	Brett Maher		Dak Prescott	CeeDee Lamb	Tony Pollard	Matt Ryan	Ezekiel Elliott	2238	16000	14000	13500	14500	12000	19.49	0	13.6	12.54	12.45	DAL	DAL	DAL	IND	DAL	1	82.725	82725	DAL	QB,FLEX,FLEX,FLEX	0
11			Deon Jackson	Peyton Hendershot	Michael Gallup		Dak Prescott	CeeDee Lamb	Tony Pollard	Matt Ryan	Michael Pittman Jr.	2239	16000	14000	13500	14500	13000	19.49	0	13.6	12.54	10.03	DAL	DAL	DAL	IND	IND	1	80.305	80305	DAL	QB,FLEX,FLEX	3
12			Jake Ferguson	Kylen Granson	Chase McLaughlin		Dak Prescott	CeeDee Lamb	Tony Pollard	Matt Ryan	Dalton Schultz	2240	16000	14000	13500	14500	7500	19.49	0	13.6	12.54	9.42	DAL	DAL	DAL	IND	DAL	1	79.695	79695	DAL	QB,FLEX,FLEX,FLEX	0
13			Peyton Hendershot	Jelani Woods	Dallas Cowboys		Dak Prescott	CeeDee Lamb	Tony Pollard	Matt Ryan	Brett Maher	2241	16000	14000	13500	14500	9500	19.49	0	13.6	12.54	9.23	DAL	DAL	DAL	IND	DAL	1	79.505	79505	DAL	QB,FLEX,FLEX,FLEX	0
14				Mo Alie-Cox			Dak Prescott	CeeDee Lamb	Tony Pollard	Matt Ryan	Michael Gallup	2242	16000	14000	13500	14500	9000	19.49	0	13.6	12.54	8.63	DAL	DAL	DAL	IND	DAL	1	78.905	78905	DAL	QB,FLEX,FLEX,FLEX	0
15							Dak Prescott	CeeDee Lamb	Tony Pollard	Matt Ryan	Chase McLaughlin	2243	16000	14000	13500	14500	8500	19.49	0	13.6	12.54	8.32	DAL	DAL	DAL	IND	IND	1	78.595	78595	DAL	QB,FLEX,FLEX	3
16							Dak Prescott	CeeDee Lamb	Tony Pollard	Matt Ryan	Dallas Cowboys	2244	16000	14000	13500	14500	10000	19.49	0	13.6	12.54	7.52	DAL	DAL	DAL	IND	DAL	1	77.795	77795	DAL	QB,FLEX,FLEX,FLEX	0
17							Dak Prescott	CeeDee Lamb	Tony Pollard	Ezekiel Elliott	Michael Pittman Jr.	2251	16000	14000	13500	12000	13000	19.49	0	13.6	12.45	10.03	DAL	DAL	DAL	DAL	IND	1	80.215	80215	DAL	QB,FLEX,FLEX,FLEX	0
18							Dak Prescott	CeeDee Lamb	Tony Pollard	Ezekiel Elliott	Dalton Schultz	2252	16000	14000	13500	12000	7500	19.49	0	13.6	12.45	9.42	DAL	DAL	DAL	DAL	DAL	1	79.605	79605	DAL	QB,FLEX,FLEX,FLEX,FLEX	0
Worksheet

Cell Formulas
Range	Formula
S2:S18	S2	=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.

 

[Peter_SSs]

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.