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))

 

[cspengel]

Well that is why I only had the information I posted in #1 as the layout is the only thing that should be the problem. The current macro should not be affecting anything in regards to how this delete method operates. The other sheets also have no effect. Sorry if I sound foward, it is just difficult to explain the whole project and the macro itself takes long to run, and it isn't something that I can just post and you can copy it and run it quickly.


I have found some code that may be working for me.

 LR = Range("G" & Rows.Count).End(xlUp).Row
Set c = Intersect(ActiveSheet.UsedRange, Range("M:M"))

Application.ScreenUpdating = False
c.Replace what:="1", replacement:="#N/A", lookat:=xlWhole
On Error Resume Next
    ActiveWorkbook.Worksheets("Worksheet").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Worksheet").Sort.SortFields.Add2 Key:=Range( _
        "M2:M400000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Worksheet").Sort
        .SetRange Range("G1:U400000")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("M1").CurrentRegion.Select
    With Selection
    ActiveSheet.Range("$G$1:$U$400000").AutoFilter Field:=7, Criteria1:="#N/A"
    .Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete
    ActiveSheet.Range("$G$1:$U$400000").AutoFilter Field:=7
    Selection.AutoFilter
On Error GoTo 0
End With

My issue I believe was with selecting the contents after the filter. So after I filter M by "1" How to select ALL of the filtered only contents and delete.

 

[cspengel]

Well that is why I only had the information I posted in #1 as the layout is the only thing that should be the problem. The current macro should not be affecting anything in regards to how this delete method operates. The other sheets also have no effect. Sorry if I sound foward, it is just difficult to explain the whole project and the macro itself takes long to run, and it isn't something that I can just post and you can copy it and run it quickly.


I have found some code that may be working for me.

 LR = Range("G" & Rows.Count).End(xlUp).Row
Set c = Intersect(ActiveSheet.UsedRange, Range("M:M"))

Application.ScreenUpdating = False
c.Replace what:="1", replacement:="#N/A", lookat:=xlWhole
On Error Resume Next
    ActiveWorkbook.Worksheets("Worksheet").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Worksheet").Sort.SortFields.Add2 Key:=Range( _
        "M2:M400000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Worksheet").Sort
        .SetRange Range("G1:U400000")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("M1").CurrentRegion.Select
    With Selection
    ActiveSheet.Range("$G$1:$U$400000").AutoFilter Field:=7, Criteria1:="#N/A"
    .Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete
    ActiveSheet.Range("$G$1:$U$400000").AutoFilter Field:=7
    Selection.AutoFilter
On Error GoTo 0
End With

My issue I believe was with selecting the contents after the filter. So after I filter M by "1" How to select ALL of the filtered only contents and delete.

I think I got it thanks for your help!

 

[Peter_SSs]

Glad you seem to have something working. I was just fishing for all the information that I could get since the code in post 4 worked for the sample data that I had (from post 1) so something had to be different between our workbooks.
Anyway - all's well that ends well.