Why is this code not deleting?

[cspengel]

I use this same code multiple times throughout my workbook with different columns, but only having problems with this one and I don't know why. Any insight would be appreciated. The purpose is replace values in column AP that are less than 1 with the number "7". The 7 is then replaced with #N/A. The values in AP are then sorted in descending order so #N/A is at top. It is then supposed to filter and delete. My set range is G:AP.

'''''''''''''''''''''''''''NO MVP UNDER 15 PROB AND 15 TARGET
 lLastRowDeDuped = Cells(Rows.Count, lFirstWriteColumn).End(xlUp).Row
    ''Add MVP Column (column AP)
    Cells(1, lLastWriteColumn + 31).Value = ChrW(931) & " MVP"
  
  With Range(Cells(2, lLastWriteColumn + 31), Cells(lLastRowDeDuped, lLastWriteColumn + 31))
   
    .Formula2R1C1 = "=COUNTIFS(RC[-15],"">15"",RC[-10],"">15"")"
        Application.Calculate
        .Value = .Value
     End With
        With Range("AP2", Range("AP" & Rows.Count).End(xlUp))
       .Value = Evaluate(Replace("if(@<1,7,if(@="""","""",@))", "@", .Address))
        End With
       
        LR = Range("G" & Rows.Count).End(xlUp).Row
Set c = Intersect(ActiveSheet.UsedRange, Range("AP:AP"))

c.Replace What:="7", replacement:="#N/A", LookAt:=xlWhole
On Error Resume Next
    ActiveWorkbook.Worksheets("Worksheet").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Worksheet").Sort.SortFields.Add2 Key:=Range( _
        "AP2:AP400000"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Worksheet").Sort
        .SetRange Range("G1:AP400000")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("AP1").CurrentRegion.Select
    With Selection
    ActiveSheet.Range("$G$1:$AP$400000").AutoFilter Field:=36, Criteria1:="#N/A"
    j = WorksheetFunction.Count(ActiveSheet.Range("$G$1:$AP$400000").Cells.SpecialCells(xlCellTypeVisible))
    If j = 0 Then
    ActiveSheet.AutoFilterMode = False
    GoTo Continue_Code14
   
    End If
   
    .Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete
    ActiveSheet.Range("$G$1:$AP$400000").AutoFilter Field:=36
    Selection.AutoFilter
    On Error GoTo 0
Continue_Code14:
    End With

As you can see from image, the values are being sorted properly, but they are not being removed.

 

[kevin9999]

Perhaps you can provide a sample of your data using the XL2BB add in so I can determine why it isn't working for you? When I run the code on what I assume is your sheet layout, it goes from this:

concat.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	AK	AL	AM	AN	AO	AP
1	hdr1	hdr2	hdr3	hdr4	hdr5	hdr6	hdr7	hdr8	hdr9	hdr10	hdr11	hdr12	hdr13	hdr14	hdr15	hdr16	hdr17	hdr18	hdr19	hdr20	hdr21	hdr22	hdr23	hdr24	hdr25	hdr26	hdr27	hdr28	hdr29	hdr30	hdr31	hdr32	hdr33	hdr34	hdr35	hdr36	hdr37	hdr38	hdr39	hdr40	hdr41	hdr42
2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	1.8
3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	0.1
4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	0.7
5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	1.2
6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	1.5
7	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7	1.9
8	8	8	8	8	8	8	8	8	8	8	8	8	8	8	8	8	8	8	8	8	8	8	8	8	8	8	8	8	8	8	8	8	8	8	8	8	8	8	8	8	8	0.2
9	9	9	9	9	9	9	9	9	9	9	9	9	9	9	9	9	9	9	9	9	9	9	9	9	9	9	9	9	9	9	9	9	9	9	9	9	9	9	9	9	9	0.2
10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	1.8
11	11	11	11	11	11	11	11	11	11	11	11	11	11	11	11	11	11	11	11	11	11	11	11	11	11	11	11	11	11	11	11	11	11	11	11	11	11	11	11	11	11	0.9
12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	1.3
13	13	13	13	13	13	13	13	13	13	13	13	13	13	13	13	13	13	13	13	13	13	13	13	13	13	13	13	13	13	13	13	13	13	13	13	13	13	13	13	13	13	0.7
14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	1.5
15	15	15	15	15	15	15	15	15	15	15	15	15	15	15	15	15	15	15	15	15	15	15	15	15	15	15	15	15	15	15	15	15	15	15	15	15	15	15	15	15	15	0.7
16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	1.6
17	17	17	17	17	17	17	17	17	17	17	17	17	17	17	17	17	17	17	17	17	17	17	17	17	17	17	17	17	17	17	17	17	17	17	17	17	17	17	17	17	17	0.6
18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	0.3
19	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19	0.7
20	20	20	20	20	20	20	20	20	20	20	20	20	20	20	20	20	20	20	20	20	20	20	20	20	20	20	20	20	20	20	20	20	20	20	20	20	20	20	20	20	20	1.5
21	21	21	21	21	21	21	21	21	21	21	21	21	21	21	21	21	21	21	21	21	21	21	21	21	21	21	21	21	21	21	21	21	21	21	21	21	21	21	21	21	21	1.0
22	22	22	22	22	22	22	22	22	22	22	22	22	22	22	22	22	22	22	22	22	22	22	22	22	22	22	22	22	22	22	22	22	22	22	22	22	22	22	22	22	22	1.0
23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	1.2
24	24	24	24	24	24	24	24	24	24	24	24	24	24	24	24	24	24	24	24	24	24	24	24	24	24	24	24	24	24	24	24	24	24	24	24	24	24	24	24	24	24	0.2
25	25	25	25	25	25	25	25	25	25	25	25	25	25	25	25	25	25	25	25	25	25	25	25	25	25	25	25	25	25	25	25	25	25	25	25	25	25	25	25	25	25	2.0
26	26	26	26	26	26	26	26	26	26	26	26	26	26	26	26	26	26	26	26	26	26	26	26	26	26	26	26	26	26	26	26	26	26	26	26	26	26	26	26	26	26	1.2
27	27	27	27	27	27	27	27	27	27	27	27	27	27	27	27	27	27	27	27	27	27	27	27	27	27	27	27	27	27	27	27	27	27	27	27	27	27	27	27	27	27	0.7
28	28	28	28	28	28	28	28	28	28	28	28	28	28	28	28	28	28	28	28	28	28	28	28	28	28	28	28	28	28	28	28	28	28	28	28	28	28	28	28	28	28	0.3
29	29	29	29	29	29	29	29	29	29	29	29	29	29	29	29	29	29	29	29	29	29	29	29	29	29	29	29	29	29	29	29	29	29	29	29	29	29	29	29	29	29	0.3
30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	30	0.4
31	31	31	31	31	31	31	31	31	31	31	31	31	31	31	31	31	31	31	31	31	31	31	31	31	31	31	31	31	31	31	31	31	31	31	31	31	31	31	31	31	31	1.5
32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	1.4
33	33	33	33	33	33	33	33	33	33	33	33	33	33	33	33	33	33	33	33	33	33	33	33	33	33	33	33	33	33	33	33	33	33	33	33	33	33	33	33	33	33	0.3
34	34	34	34	34	34	34	34	34	34	34	34	34	34	34	34	34	34	34	34	34	34	34	34	34	34	34	34	34	34	34	34	34	34	34	34	34	34	34	34	34	34	1.4
35	35	35	35	35	35	35	35	35	35	35	35	35	35	35	35	35	35	35	35	35	35	35	35	35	35	35	35	35	35	35	35	35	35	35	35	35	35	35	35	35	35	0.2
36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	1.2
37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	1.2
38	38	38	38	38	38	38	38	38	38	38	38	38	38	38	38	38	38	38	38	38	38	38	38	38	38	38	38	38	38	38	38	38	38	38	38	38	38	38	38	38	38	0.5
39	39	39	39	39	39	39	39	39	39	39	39	39	39	39	39	39	39	39	39	39	39	39	39	39	39	39	39	39	39	39	39	39	39	39	39	39	39	39	39	39	39	1.5
40	40	40	40	40	40	40	40	40	40	40	40	40	40	40	40	40	40	40	40	40	40	40	40	40	40	40	40	40	40	40	40	40	40	40	40	40	40	40	40	40	40	1.7
41	41	41	41	41	41	41	41	41	41	41	41	41	41	41	41	41	41	41	41	41	41	41	41	41	41	41	41	41	41	41	41	41	41	41	41	41	41	41	41	41	41	1.7
42	42	42	42	42	42	42	42	42	42	42	42	42	42	42	42	42	42	42	42	42	42	42	42	42	42	42	42	42	42	42	42	42	42	42	42	42	42	42	42	42	42	1.1
43	43	43	43	43	43	43	43	43	43	43	43	43	43	43	43	43	43	43	43	43	43	43	43	43	43	43	43	43	43	43	43	43	43	43	43	43	43	43	43	43	43	1.3
44	44	44	44	44	44	44	44	44	44	44	44	44	44	44	44	44	44	44	44	44	44	44	44	44	44	44	44	44	44	44	44	44	44	44	44	44	44	44	44	44	44	1.7
45	45	45	45	45	45	45	45	45	45	45	45	45	45	45	45	45	45	45	45	45	45	45	45	45	45	45	45	45	45	45	45	45	45	45	45	45	45	45	45	45	45	0.9
46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	1.1
47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	1.3
48	48	48	48	48	48	48	48	48	48	48	48	48	48	48	48	48	48	48	48	48	48	48	48	48	48	48	48	48	48	48	48	48	48	48	48	48	48	48	48	48	48	1.4
49	49	49	49	49	49	49	49	49	49	49	49	49	49	49	49	49	49	49	49	49	49	49	49	49	49	49	49	49	49	49	49	49	49	49	49	49	49	49	49	49	49	1.9
50	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50	1.7
51	51	51	51	51	51	51	51	51	51	51	51	51	51	51	51	51	51	51	51	51	51	51	51	51	51	51	51	51	51	51	51	51	51	51	51	51	51	51	51	51	51	0.4
52	52	52	52	52	52	52	52	52	52	52	52	52	52	52	52	52	52	52	52	52	52	52	52	52	52	52	52	52	52	52	52	52	52	52	52	52	52	52	52	52	52	0.0
53	53	53	53	53	53	53	53	53	53	53	53	53	53	53	53	53	53	53	53	53	53	53	53	53	53	53	53	53	53	53	53	53	53	53	53	53	53	53	53	53	53	0.1
54	54	54	54	54	54	54	54	54	54	54	54	54	54	54	54	54	54	54	54	54	54	54	54	54	54	54	54	54	54	54	54	54	54	54	54	54	54	54	54	54	54	0.2
55	55	55	55	55	55	55	55	55	55	55	55	55	55	55	55	55	55	55	55	55	55	55	55	55	55	55	55	55	55	55	55	55	55	55	55	55	55	55	55	55	55	1.1
56	56	56	56	56	56	56	56	56	56	56	56	56	56	56	56	56	56	56	56	56	56	56	56	56	56	56	56	56	56	56	56	56	56	56	56	56	56	56	56	56	56	1.7
57	57	57	57	57	57	57	57	57	57	57	57	57	57	57	57	57	57	57	57	57	57	57	57	57	57	57	57	57	57	57	57	57	57	57	57	57	57	57	57	57	57	1.7
58	58	58	58	58	58	58	58	58	58	58	58	58	58	58	58	58	58	58	58	58	58	58	58	58	58	58	58	58	58	58	58	58	58	58	58	58	58	58	58	58	58	0.9
59	59	59	59	59	59	59	59	59	59	59	59	59	59	59	59	59	59	59	59	59	59	59	59	59	59	59	59	59	59	59	59	59	59	59	59	59	59	59	59	59	59	0.3
60	60	60	60	60	60	60	60	60	60	60	60	60	60	60	60	60	60	60	60	60	60	60	60	60	60	60	60	60	60	60	60	60	60	60	60	60	60	60	60	60	60	1.0
61	61	61	61	61	61	61	61	61	61	61	61	61	61	61	61	61	61	61	61	61	61	61	61	61	61	61	61	61	61	61	61	61	61	61	61	61	61	61	61	61	61	0.5
62	62	62	62	62	62	62	62	62	62	62	62	62	62	62	62	62	62	62	62	62	62	62	62	62	62	62	62	62	62	62	62	62	62	62	62	62	62	62	62	62	62	1.5
63	63	63	63	63	63	63	63	63	63	63	63	63	63	63	63	63	63	63	63	63	63	63	63	63	63	63	63	63	63	63	63	63	63	63	63	63	63	63	63	63	63	1.3
64	64	64	64	64	64	64	64	64	64	64	64	64	64	64	64	64	64	64	64	64	64	64	64	64	64	64	64	64	64	64	64	64	64	64	64	64	64	64	64	64	64	1.4
65	65	65	65	65	65	65	65	65	65	65	65	65	65	65	65	65	65	65	65	65	65	65	65	65	65	65	65	65	65	65	65	65	65	65	65	65	65	65	65	65	65	0.3
66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	1.3
67	67	67	67	67	67	67	67	67	67	67	67	67	67	67	67	67	67	67	67	67	67	67	67	67	67	67	67	67	67	67	67	67	67	67	67	67	67	67	67	67	67	1.3
68	68	68	68	68	68	68	68	68	68	68	68	68	68	68	68	68	68	68	68	68	68	68	68	68	68	68	68	68	68	68	68	68	68	68	68	68	68	68	68	68	68	1.1
69	69	69	69	69	69	69	69	69	69	69	69	69	69	69	69	69	69	69	69	69	69	69	69	69	69	69	69	69	69	69	69	69	69	69	69	69	69	69	69	69	69	0.6
70	70	70	70	70	70	70	70	70	70	70	70	70	70	70	70	70	70	70	70	70	70	70	70	70	70	70	70	70	70	70	70	70	70	70	70	70	70	70	70	70	70	1.6
Sheet2

To this:

concat.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	AK	AL	AM	AN	AO	AP
1	hdr1	hdr2	hdr3	hdr4	hdr5	hdr6	hdr7	hdr8	hdr9	hdr10	hdr11	hdr12	hdr13	hdr14	hdr15	hdr16	hdr17	hdr18	hdr19	hdr20	hdr21	hdr22	hdr23	hdr24	hdr25	hdr26	hdr27	hdr28	hdr29	hdr30	hdr31	hdr32	hdr33	hdr34	hdr35	hdr36	hdr37	hdr38	hdr39	hdr40	hdr41	hdr42
2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	1.8
3	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	1.2
4	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	1.5
5	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7	1.9
6	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	1.8
7	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	12	1.3
8	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14	1.5
9	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	1.6
10	20	20	20	20	20	20	20	20	20	20	20	20	20	20	20	20	20	20	20	20	20	20	20	20	20	20	20	20	20	20	20	20	20	20	20	20	20	20	20	20	20	1.5
11	22	22	22	22	22	22	22	22	22	22	22	22	22	22	22	22	22	22	22	22	22	22	22	22	22	22	22	22	22	22	22	22	22	22	22	22	22	22	22	22	22	1.0
12	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	1.2
13	25	25	25	25	25	25	25	25	25	25	25	25	25	25	25	25	25	25	25	25	25	25	25	25	25	25	25	25	25	25	25	25	25	25	25	25	25	25	25	25	25	2.0
14	26	26	26	26	26	26	26	26	26	26	26	26	26	26	26	26	26	26	26	26	26	26	26	26	26	26	26	26	26	26	26	26	26	26	26	26	26	26	26	26	26	1.2
15	31	31	31	31	31	31	31	31	31	31	31	31	31	31	31	31	31	31	31	31	31	31	31	31	31	31	31	31	31	31	31	31	31	31	31	31	31	31	31	31	31	1.5
16	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	32	1.4
17	34	34	34	34	34	34	34	34	34	34	34	34	34	34	34	34	34	34	34	34	34	34	34	34	34	34	34	34	34	34	34	34	34	34	34	34	34	34	34	34	34	1.4
18	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	1.2
19	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	1.2
20	39	39	39	39	39	39	39	39	39	39	39	39	39	39	39	39	39	39	39	39	39	39	39	39	39	39	39	39	39	39	39	39	39	39	39	39	39	39	39	39	39	1.5
21	40	40	40	40	40	40	40	40	40	40	40	40	40	40	40	40	40	40	40	40	40	40	40	40	40	40	40	40	40	40	40	40	40	40	40	40	40	40	40	40	40	1.7
22	41	41	41	41	41	41	41	41	41	41	41	41	41	41	41	41	41	41	41	41	41	41	41	41	41	41	41	41	41	41	41	41	41	41	41	41	41	41	41	41	41	1.7
23	42	42	42	42	42	42	42	42	42	42	42	42	42	42	42	42	42	42	42	42	42	42	42	42	42	42	42	42	42	42	42	42	42	42	42	42	42	42	42	42	42	1.1
24	43	43	43	43	43	43	43	43	43	43	43	43	43	43	43	43	43	43	43	43	43	43	43	43	43	43	43	43	43	43	43	43	43	43	43	43	43	43	43	43	43	1.3
25	44	44	44	44	44	44	44	44	44	44	44	44	44	44	44	44	44	44	44	44	44	44	44	44	44	44	44	44	44	44	44	44	44	44	44	44	44	44	44	44	44	1.7
26	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	1.1
27	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	1.3
28	48	48	48	48	48	48	48	48	48	48	48	48	48	48	48	48	48	48	48	48	48	48	48	48	48	48	48	48	48	48	48	48	48	48	48	48	48	48	48	48	48	1.4
29	49	49	49	49	49	49	49	49	49	49	49	49	49	49	49	49	49	49	49	49	49	49	49	49	49	49	49	49	49	49	49	49	49	49	49	49	49	49	49	49	49	1.9
30	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50	1.7
31	55	55	55	55	55	55	55	55	55	55	55	55	55	55	55	55	55	55	55	55	55	55	55	55	55	55	55	55	55	55	55	55	55	55	55	55	55	55	55	55	55	1.1
32	56	56	56	56	56	56	56	56	56	56	56	56	56	56	56	56	56	56	56	56	56	56	56	56	56	56	56	56	56	56	56	56	56	56	56	56	56	56	56	56	56	1.7
33	57	57	57	57	57	57	57	57	57	57	57	57	57	57	57	57	57	57	57	57	57	57	57	57	57	57	57	57	57	57	57	57	57	57	57	57	57	57	57	57	57	1.7
34	62	62	62	62	62	62	62	62	62	62	62	62	62	62	62	62	62	62	62	62	62	62	62	62	62	62	62	62	62	62	62	62	62	62	62	62	62	62	62	62	62	1.5
35	63	63	63	63	63	63	63	63	63	63	63	63	63	63	63	63	63	63	63	63	63	63	63	63	63	63	63	63	63	63	63	63	63	63	63	63	63	63	63	63	63	1.3
36	64	64	64	64	64	64	64	64	64	64	64	64	64	64	64	64	64	64	64	64	64	64	64	64	64	64	64	64	64	64	64	64	64	64	64	64	64	64	64	64	64	1.4
37	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	1.3
38	67	67	67	67	67	67	67	67	67	67	67	67	67	67	67	67	67	67	67	67	67	67	67	67	67	67	67	67	67	67	67	67	67	67	67	67	67	67	67	67	67	1.3
39	68	68	68	68	68	68	68	68	68	68	68	68	68	68	68	68	68	68	68	68	68	68	68	68	68	68	68	68	68	68	68	68	68	68	68	68	68	68	68	68	68	1.1
40	70	70	70	70	70	70	70	70	70	70	70	70	70	70	70	70	70	70	70	70	70	70	70	70	70	70	70	70	70	70	70	70	70	70	70	70	70	70	70	70	70	1.6
Sheet2

As you can see, the cells in columns A:E are untouched, and the headers are not deleted.

 

[cspengel]

I'll try getting something attached here in a few minutes. Was having issues with excel blocking xl2bb

 

[cspengel]

disregard2NewestMonTest(DONOTUSE)4.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	AK	AL	AM	AN	AO	AP
1	QB	FLEX	FLEX	FLEX	FLEX		QB	FLEX	FLEX	FLEX	FLEX	ComboID	Σ Salary	Σ Projection	Σ Probability	Σ Target	Σ Depth	Σ #Restriction	Σ Ceiling Value	Σ Value	Σ Stack	Σ Stack POS	Σ Commas	Σ Filter	Σ Player1	Σ Player2	QB	FLEX	FLEX	FLEX	FLEX	QB	FLEX	FLEX	FLEX	FLEX	QB	FLEX	FLEX	FLEX	FLEX	Σ MVP
2	Justin Herbert	Justin Herbert	Justin Herbert	Justin Herbert	Zack Moss		Keenan Allen	Justin Herbert	Deon Jackson	Chase McLaughlin	Indianapolis Colts	66463	58500	65.525	153.18	60.46	5	3	1.300683761	1.120085	IND	FLEX,FLEX,FLEX	3	0			27.68	26.31	35.86	24.32	23.68	13.66	18.39	7.38	13.02	7.95	WR	QB	RB	K	D	0
3	Austin Ekeler	Austin Ekeler	Austin Ekeler	Austin Ekeler	Chase McLaughlin		Mike Williams	Justin Herbert	Deon Jackson	Chase McLaughlin	Indianapolis Colts	131143	57500	64.985	156.855	59.41	6	3	1.397043478	1.130173	IND	FLEX,FLEX,FLEX	3	0			30.27	26.31	35.86	24.32	23.68	12.79	18.39	7.38	13.02	7.95	WR	QB	RB	K	D	0
4	Keenan Allen	Keenan Allen	Keenan Allen	Keenan Allen	Cameron Dicker		Justin Herbert	Keenan Allen	Deon Jackson	Chase McLaughlin	Indianapolis Colts	11023	58500	69.425	152.985	60.46	5	3	1.300683761	1.186752	IND	FLEX,FLEX,FLEX	3	0			26.31	27.68	35.86	24.32	23.68	18.39	13.66	7.38	13.02	7.95	QB	WR	RB	K	D	0
5	Nick Foles	Nick Foles	Nick Foles	Nick Foles	Gerald Everett		Justin Herbert	Mike Williams	Deon Jackson	Chase McLaughlin	Indianapolis Colts	20263	57500	69.065	155.435	59.41	6	3	1.397043478	1.20113	IND	FLEX,FLEX,FLEX	3	0			26.31	30.27	35.86	24.32	23.68	18.39	12.79	7.38	13.02	7.95	QB	WR	RB	K	D	0
6	Mike Williams	Mike Williams	Mike Williams	Mike Williams	Joshua Palmer		Mike Williams	Keenan Allen	Deon Jackson	Chase McLaughlin	Indianapolis Colts	140383	55000	57.185	157.245	54.82	6	3	1.333636364	1.039727	IND	FLEX,FLEX,FLEX	3	0			30.27	27.68	35.86	24.32	23.68	12.79	13.66	7.38	13.02	7.95	WR	WR	RB	K	D	0
7	Deon Jackson	Deon Jackson	Deon Jackson	Deon Jackson	Parris Campbell		Keenan Allen	Mike Williams	Deon Jackson	Chase McLaughlin	Indianapolis Colts	84943	55000	57.365	156.02	54.82	6	3	1.333636364	1.043	IND	FLEX,FLEX,FLEX	3	0			27.68	30.27	35.86	24.32	23.68	13.66	12.79	7.38	13.02	7.95	WR	WR	RB	K	D	0
8	Michael Pittman Jr.	Michael Pittman Jr.	Michael Pittman Jr.	Michael Pittman Jr.	Alec Pierce		Justin Herbert	Deon Jackson	Chase McLaughlin	Parris Campbell	Indianapolis Colts	25933	54000	62.105	150.825	58.06	6	3	1.397962963	1.150092	IND	FLEX,FLEX,FLEX,FLEX	4	0			26.31	35.86	24.32	25.41	23.68	18.39	7.38	13.02	11.31	7.95	QB	RB	K	WR	D	0
9			Zack Moss	Zack Moss	Joshua Kelley		Justin Herbert	Deon Jackson	Michael Pittman Jr.	Chase McLaughlin	Indianapolis Colts	25213	58000	66.925	159.405	57.54	5	3	1.31137931	1.153879	IND	FLEX,FLEX,FLEX,FLEX	4	0			26.31	35.86	35.12	24.32	23.68	18.39	7.38	10.66	13.02	7.95	QB	RB	WR	K	D	0
10			Chase McLaughlin	Chase McLaughlin	DeAndre Carter		Keenan Allen	Justin Herbert	Deon Jackson	Parris Campbell	Indianapolis Colts	66523	58000	61.495	154.02	58.7	6	3	1.295344828	1.060258	IND	FLEX,FLEX,FLEX	3	0			27.68	26.31	35.86	25.41	23.68	13.66	18.39	7.38	11.31	7.95	WR	QB	RB	WR	D	0
11			Cameron Dicker	Cameron Dicker	Donald Parham Jr.		Mike Williams	Justin Herbert	Deon Jackson	Parris Campbell	Indianapolis Colts	131203	57000	60.955	157.695	57.65	7	3	1.39245614	1.069385	IND	FLEX,FLEX,FLEX	3	0			30.27	26.31	35.86	25.41	23.68	12.79	18.39	7.38	11.31	7.95	WR	QB	RB	WR	D	0
12			Gerald Everett	Gerald Everett	Jelani Woods		Keenan Allen	Justin Herbert	Deon Jackson	Alec Pierce	Indianapolis Colts	66538	57000	61.115	151.48	58.17	7	3	1.271754386	1.072192	IND	FLEX,FLEX,FLEX	3	0			27.68	26.31	35.86	22.31	23.68	13.66	18.39	7.38	10.75	7.95	WR	QB	RB	WR	D	0
13			Joshua Palmer	Joshua Palmer	Los Angeles Chargers		Mike Williams	Justin Herbert	Deon Jackson	Alec Pierce	Indianapolis Colts	131218	56000	60.575	155.155	57.12	8	3	1.370178571	1.081696	IND	FLEX,FLEX,FLEX	3	0			30.27	26.31	35.86	22.31	23.68	12.79	18.39	7.38	10.75	7.95	WR	QB	RB	WR	D	0
14			Parris Campbell	Parris Campbell	Indianapolis Colts		Justin Herbert	Keenan Allen	Deon Jackson	Parris Campbell	Indianapolis Colts	11083	58000	65.395	153.825	58.7	6	3	1.295344828	1.1275	IND	FLEX,FLEX,FLEX	3	0			26.31	27.68	35.86	25.41	23.68	18.39	13.66	7.38	11.31	7.95	QB	WR	RB	WR	D	0
15			Alec Pierce	Alec Pierce	Ashton Dulin		Justin Herbert	Keenan Allen	Deon Jackson	Alec Pierce	Indianapolis Colts	11098	57000	65.015	151.285	58.17	7	3	1.271754386	1.140614	IND	FLEX,FLEX,FLEX	3	0			26.31	27.68	35.86	22.31	23.68	18.39	13.66	7.38	10.75	7.95	QB	WR	RB	WR	D	0
16				Joshua Kelley	Jordan Wilkins		Justin Herbert	Mike Williams	Deon Jackson	Parris Campbell	Indianapolis Colts	20323	57000	65.035	156.275	57.65	7	3	1.39245614	1.140964	IND	FLEX,FLEX,FLEX	3	0			26.31	30.27	35.86	25.41	23.68	18.39	12.79	7.38	11.31	7.95	QB	WR	RB	WR	D	0
17				DeAndre Carter			Justin Herbert	Mike Williams	Deon Jackson	Alec Pierce	Indianapolis Colts	20338	56000	64.655	153.735	57.12	8	3	1.370178571	1.154553	IND	FLEX,FLEX,FLEX	3	0			26.31	30.27	35.86	22.31	23.68	18.39	12.79	7.38	10.75	7.95	QB	WR	RB	WR	D	0
18				Donald Parham Jr.			Keenan Allen	Deon Jackson	Michael Pittman Jr.	Chase McLaughlin	Indianapolis Colts	89893	55500	55.225	159.99	52.95	5	3	1.244684685	0.995045	IND	FLEX,FLEX,FLEX,FLEX	4	0			27.68	35.86	35.12	24.32	23.68	13.66	7.38	10.66	13.02	7.95	WR	RB	WR	K	D	0
19				Jelani Woods			Mike Williams	Deon Jackson	Michael Pittman Jr.	Chase McLaughlin	Indianapolis Colts	154573	54500	54.685	163.665	51.9	6	3	1.345321101	1.003394	IND	FLEX,FLEX,FLEX,FLEX	4	0			30.27	35.86	35.12	24.32	23.68	12.79	7.38	10.66	13.02	7.95	WR	RB	WR	K	D	0
20				Los Angeles Chargers			Mike Williams	Keenan Allen	Deon Jackson	Parris Campbell	Indianapolis Colts	140443	54500	53.155	158.085	53.06	7	3	1.328256881	0.975321	IND	FLEX,FLEX,FLEX	3	0			30.27	27.68	35.86	25.41	23.68	12.79	13.66	7.38	11.31	7.95	WR	WR	RB	WR	D	0
21				Indianapolis Colts			Keenan Allen	Mike Williams	Deon Jackson	Parris Campbell	Indianapolis Colts	85003	54500	53.335	156.86	53.06	7	3	1.328256881	0.978623	IND	FLEX,FLEX,FLEX	3	0			27.68	30.27	35.86	25.41	23.68	13.66	12.79	7.38	11.31	7.95	WR	WR	RB	WR	D	0
22				Ashton Dulin			Mike Williams	Keenan Allen	Deon Jackson	Michael Pittman Jr.	Indianapolis Colts	140353	58500	57.975	166.665	52.54	6	3	1.247179487	0.991025	IND	FLEX,FLEX,FLEX	3	0			30.27	27.68	35.86	35.12	23.68	12.79	13.66	7.38	10.66	7.95	WR	WR	RB	WR	D	0
23				Jordan Wilkins			Keenan Allen	Mike Williams	Deon Jackson	Michael Pittman Jr.	Indianapolis Colts	84913	58500	58.155	165.44	52.54	6	3	1.247179487	0.994102	IND	FLEX,FLEX,FLEX	3	0			27.68	30.27	35.86	35.12	23.68	13.66	12.79	7.38	10.66	7.95	WR	WR	RB	WR	D	1
24							Justin Herbert	Deon Jackson	Michael Pittman Jr.	Parris Campbell	Indianapolis Colts	25273	57500	62.895	160.245	55.78	6	3	1.306086957	1.093826	IND	FLEX,FLEX,FLEX,FLEX	4	0			26.31	35.86	35.12	25.41	23.68	18.39	7.38	10.66	11.31	7.95	QB	RB	WR	WR	D	1
25							Justin Herbert	Deon Jackson	Michael Pittman Jr.	Alec Pierce	Indianapolis Colts	25288	56500	62.515	157.705	55.25	7	3	1.282477876	1.10646	IND	FLEX,FLEX,FLEX,FLEX	4	0			26.31	35.86	35.12	22.31	23.68	18.39	7.38	10.66	10.75	7.95	QB	RB	WR	WR	D	1
26							Keenan Allen	Deon Jackson	Michael Pittman Jr.	Parris Campbell	Indianapolis Colts	89953	55000	51.195	160.83	51.19	6	3	1.238545455	0.930818	IND	FLEX,FLEX,FLEX,FLEX	4	0			27.68	35.86	35.12	25.41	23.68	13.66	7.38	10.66	11.31	7.95	WR	RB	WR	WR	D	1
27							Mike Williams	Deon Jackson	Michael Pittman Jr.	Parris Campbell	Indianapolis Colts	154633	54000	50.655	164.505	50.14	7	3	1.34	0.938055	IND	FLEX,FLEX,FLEX,FLEX	4	0			30.27	35.86	35.12	25.41	23.68	12.79	7.38	10.66	11.31	7.95	WR	RB	WR	WR	D	1
28							Keenan Allen	Deon Jackson	Michael Pittman Jr.	Alec Pierce	Indianapolis Colts	89968	54000	50.815	158.29	50.66	7	3	1.212592593	0.941018	IND	FLEX,FLEX,FLEX,FLEX	4	0			27.68	35.86	35.12	22.31	23.68	13.66	7.38	10.66	10.75	7.95	WR	RB	WR	WR	D	1
29							Keenan Allen	Austin Ekeler	Deon Jackson	Chase McLaughlin	Indianapolis Colts	71083	59000	62.375	155.88	72.97	5	3	1.374067797	1.057203	IND	FLEX,FLEX,FLEX	3	0			27.68	29.86	35.86	24.32	23.68	13.66	30.85	7.38	13.02	7.95	WR	RB	RB	K	D	1
30							Mike Williams	Austin Ekeler	Deon Jackson	Chase McLaughlin	Indianapolis Colts	135763	58000	61.835	159.555	71.92	6	3	1.470862069	1.06612	IND	FLEX,FLEX,FLEX	3	0			30.27	29.86	35.86	24.32	23.68	12.79	30.85	7.38	13.02	7.95	WR	RB	RB	K	D	1
31							Austin Ekeler	Keenan Allen	Deon Jackson	Chase McLaughlin	Indianapolis Colts	43363	59000	64.7	157.035	72.97	5	3	1.374067797	1.09661	IND	FLEX,FLEX,FLEX	3	0			29.86	27.68	35.86	24.32	23.68	30.85	13.66	7.38	13.02	7.95	RB	WR	RB	K	D	1
32							Austin Ekeler	Mike Williams	Deon Jackson	Chase McLaughlin	Indianapolis Colts	52603	58000	64.34	159.485	71.92	6	3	1.470862069	1.10931	IND	FLEX,FLEX,FLEX	3	0			29.86	30.27	35.86	24.32	23.68	30.85	12.79	7.38	13.02	7.95	RB	WR	RB	K	D	1
33							Austin Ekeler	Justin Herbert	Deon Jackson	Alec Pierce	Indianapolis Colts	34198	60000	68.09	154.945	75.27	7	3	1.4075	1.134833	IND	FLEX,FLEX,FLEX	3	0			29.86	26.31	35.86	22.31	23.68	30.85	18.39	7.38	10.75	7.95	RB	QB	RB	WR	D	1
34							Justin Herbert	Austin Ekeler	Deon Jackson	Alec Pierce	Indianapolis Colts	6478	60000	69.665	153.595	75.27	7	3	1.4075	1.161083	IND	FLEX,FLEX,FLEX	3	0			26.31	29.86	35.86	22.31	23.68	18.39	30.85	7.38	10.75	7.95	QB	RB	RB	WR	D	1
35							Austin Ekeler	Deon Jackson	Chase McLaughlin	Parris Campbell	Indianapolis Colts	58273	54500	57.38	154.875	70.57	6	3	1.476513761	1.052844	IND	FLEX,FLEX,FLEX,FLEX	4	0			29.86	35.86	24.32	25.41	23.68	30.85	7.38	13.02	11.31	7.95	RB	RB	K	WR	D	1
36							Austin Ekeler	Deon Jackson	Michael Pittman Jr.	Chase McLaughlin	Indianapolis Colts	57553	58500	62.2	163.455	70.05	5	3	1.385299145	1.063247	IND	FLEX,FLEX,FLEX,FLEX	4	0			29.86	35.86	35.12	24.32	23.68	30.85	7.38	10.66	13.02	7.95	RB	RB	WR	K	D	1
37							Keenan Allen	Austin Ekeler	Deon Jackson	Parris Campbell	Indianapolis Colts	71143	58500	58.345	156.72	71.21	6	3	1.369401709	0.99735	IND	FLEX,FLEX,FLEX	3	0			27.68	29.86	35.86	25.41	23.68	13.66	30.85	7.38	11.31	7.95	WR	RB	RB	WR	D	1
38							Mike Williams	Austin Ekeler	Deon Jackson	Parris Campbell	Indianapolis Colts	135823	57500	57.805	160.395	70.16	7	3	1.466956522	1.005304	IND	FLEX,FLEX,FLEX	3	0			30.27	29.86	35.86	25.41	23.68	12.79	30.85	7.38	11.31	7.95	WR	RB	RB	WR	D	1
39							Keenan Allen	Austin Ekeler	Deon Jackson	Alec Pierce	Indianapolis Colts	71158	57500	57.965	154.18	70.68	7	3	1.347304348	1.008086	IND	FLEX,FLEX,FLEX	3	0			27.68	29.86	35.86	22.31	23.68	13.66	30.85	7.38	10.75	7.95	WR	RB	RB	WR	D	1
40							Mike Williams	Austin Ekeler	Deon Jackson	Alec Pierce	Indianapolis Colts	135838	56500	57.425	157.855	69.63	8	3	1.44619469	1.016371	IND	FLEX,FLEX,FLEX	3	0			30.27	29.86	35.86	22.31	23.68	12.79	30.85	7.38	10.75	7.95	WR	RB	RB	WR	D	1
Worksheet

Cell Formulas
Range	Formula
X2:X40	X2	=IF($Z$2="",COUNTIF(G2:K2,$Y$2),COUNTIF(G2:K2,$Y$2)*COUNTIF(G2:K2,$Z$2))

 

[kevin9999]

Try this:

Option Explicit
Sub DeleteOnAP_V3()
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet2")   '<~~ ** Change this to actual sheet name **
    Dim r As Range
    Set r = Intersect(ws.UsedRange, ws.Range("G:AP"))
    r.Select
    
    With r
        .AutoFilter 36, "<1"
        If r.Cells(Rows.Count, 36).End(xlUp).Row > 1 Then
            .Offset(1).Resize(.Rows.Count - 1).Value2 = ""
            .AutoFilter
            .Offset(1).Resize(.Rows.Count - 1).SpecialCells(4).Delete shift:=xlUp
        Else
            .AutoFilter
        End If
    End With
    Application.ScreenUpdating = True
End Sub

 

[cspengel]

Try this:

Option Explicit
Sub DeleteOnAP_V3()
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet2")   '<~~ ** Change this to actual sheet name **
    Dim r As Range
    Set r = Intersect(ws.UsedRange, ws.Range("G:AP"))
    r.Select
   
    With r
        .AutoFilter 36, "<1"
        If r.Cells(Rows.Count, 36).End(xlUp).Row > 1 Then
            .Offset(1).Resize(.Rows.Count - 1).Value2 = ""
            .AutoFilter
            .Offset(1).Resize(.Rows.Count - 1).SpecialCells(4).Delete shift:=xlUp
        Else
            .AutoFilter
        End If
    End With
    Application.ScreenUpdating = True
End Sub

Awesome kevin! That worked! Mind explaining how that worked? hah thanks so much!

 

[kevin9999]

Awesome kevin! That worked! Mind explaining how that worked? hah thanks so much!

Once the filter was applied, it filled every visible cell in the range G:AP (from row 2 down) with a blank ("") and then selected SpecialCells(4) (= blank cells) and deleted the cells - shifting up after deletion. The test as to whether any rows were found with the filter <1 in column AP was: "If r.Cells(Rows.Count, 36).End(xlUp).Row > 1 Then" which asks if, after selecting the last cell in the column & using the End(xlUp) command - did it go to row 1 (which means that no values were found in the filter) so if it went to a row greater than 1 ( If.....> 1 Then) then execute the code within the If statement - otherwise (Else), go straight to turning the Autofilter off. I hope that helps.