# Why is this code not deleting?



## cspengel (Dec 28, 2022)

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.


----------



## cspengel (Dec 28, 2022)

Okay, what appears to be stopping it from deleting is : 


```
If j = 0 Then
    ActiveSheet.AutoFilterMode = False
    GoTo Continue_Code14
```

If I remove that it works fine. Can anyone explain why it would stop it from working if j isn't =  0?


----------



## kevin9999 (Dec 28, 2022)

Is it simply the case that you want to delete any row (entire row) where the value in column AP is less than one?  Also, what is the significance of setting the last row to 400000?  Do you have other data below this you want to leave untouched?


----------



## cspengel (Dec 28, 2022)

kevin9999 said:


> Is it simply the case that you want to delete any row (entire row) where the value in column AP is less than one?  Also, what is the significance of setting the last row to 400000?  Do you have other data below this you want to leave untouched?


Yes Kevin. My main issue is without the check on some columns, My macro fails to produce results as sometimes j does = 0 and then it does delete all my information. Can you assist in telling me if I have that part of the code written wrong or if there is another way to write it. And no the 400000 has no significance. I just didn't utilize LR(last row) for that part, but it was to be sure everything that filtered is deleted.


----------



## kevin9999 (Dec 28, 2022)

I don't know where this will fit into the rest of your code (because you haven't shown it all) but try the following on a *copy *of your data.  Just change the sheet name to the actual sheet name.


```
Option Explicit
Sub DeleteOnAP()
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet2")   '<~~ ** Change this to actual sheet name **
    Dim LCol As Long, i As Long
    Dim ArrIn, ArrOut
    
    LCol = ws.Cells.Find("*", , xlFormulas, , 2, 2).Column + 1
    ArrIn = ws.Range("AP2", Cells(Rows.Count, "AP").End(xlUp))
    ReDim ArrOut(1 To UBound(ArrIn), 1 To 1)
    
    For i = 1 To UBound(ArrIn)
        If ArrIn(i, 1) < 1 Then ArrOut(i, 1) = 1
    Next i

    Cells(2, LCol).Resize(UBound(ArrIn)) = ArrOut
    i = WorksheetFunction.Sum(Columns(LCol))
    If i > 0 Then
        ws.UsedRange.Offset(1).Resize(ws.Rows.Count - 1).Sort _
        Key1:=Cells(2, LCol), order1:=1, Header:=2
         Cells(2, LCol).Resize(i).EntireRow.Delete
    End If
    Application.ScreenUpdating = True
End Sub
```


----------



## cspengel (Dec 28, 2022)

Sorry for not being clear regarding the deletion of entire row. My full code is part of a larger project that is approx 2300 lines. Didn't want to cause more confusion.*  I believe* you may have tried to assist me before regarding a similar issue and the code I posted is what I ended up going with instead as I was having issues with it deleting more than it should. I have data in columns A:E and the code you provided deletes the entire row instead of the just the selected Cells in range G:AP.

Do you have any suggestions on just modifying the code I provided to just do a check if there is data after being filtered. Thanks you!


----------



## kevin9999 (Dec 28, 2022)

kevin9999 said:


> Is it simply the case that you want to delete any row (*entire row*) where the value in column AP is less than one?





cspengel said:


> Yes Kevin.


OK, let's try again.  Do you want the cells in columns G:AP _deleted _or simply _cleared_?  I suspect you just want them cleared, in which case there's easier ways to achieve that result.


----------



## cspengel (Dec 28, 2022)

Sorry. If by cleared you mean when it becomes unfiltered, you will have blanks where the data once was. (I don't want that) The code I posted deletes the cells between g:ap and shifts cells in that range up which is what I want. So I don't want the entire row deleted. I want the cells between g:ap deleted where everything in that range shifts up. All that works in my code. My only issue is when it checks if there is data in the filtered range it seems to  unfilter the range as if j returned as 0 when in fact there was data to be deleted.

But it is strange, because in other columns, it will delete just fine. It leads me to the conclusions the if-then is not written correctly.


----------



## kevin9999 (Dec 28, 2022)

Perhaps this?

```
Option Explicit
Sub DeleteOnAP_V2()
    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"))
   
    With r
        .AutoFilter 36, "<1"
        If r.Cells(Rows.Count, 36).End(xlUp).Row > 1 Then
            .Offset(1).Resize(.Rows.Count - 1).Delete shift:=xlUp
        End If
        .AutoFilter
    End With
    Application.ScreenUpdating = True
End Sub
```


----------



## cspengel (Dec 28, 2022)

Unfortunately that did not work either and I don't know why. It still deleted data out of range. (in columns A:E). Also removed the header of AP & removed all data as if the check was not in place. I Think I am just going to have to go through each column and see which one will allow the check. It doesn't make much sense to me - if the check is not there, the macro will delete everything if there is nothing to be deleted, if it is there in a column where stuff is to be deleted, it wont delete it. If I sound confusing it is because I am confused haha. Sorry to waste your time. I'll mark your post as solution as maybe it can help someone.


----------



## cspengel (Dec 28, 2022)

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 (Dec 29, 2022)

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.xlsmABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAP1hdr1hdr2hdr3hdr4hdr5hdr6hdr7hdr8hdr9hdr10hdr11hdr12hdr13hdr14hdr15hdr16hdr17hdr18hdr19hdr20hdr21hdr22hdr23hdr24hdr25hdr26hdr27hdr28hdr29hdr30hdr31hdr32hdr33hdr34hdr35hdr36hdr37hdr38hdr39hdr40hdr41hdr422222222222222222222222222222222222222222221.83333333333333333333333333333333333333333330.14444444444444444444444444444444444444444440.75555555555555555555555555555555555555555551.26666666666666666666666666666666666666666661.57777777777777777777777777777777777777777771.98888888888888888888888888888888888888888880.29999999999999999999999999999999999999999990.21010101010101010101010101010101010101010101010101010101010101010101010101010101010101.81111111111111111111111111111111111111111111111111111111111111111111111111111111111110.91212121212121212121212121212121212121212121212121212121212121212121212121212121212121.31313131313131313131313131313131313131313131313131313131313131313131313131313131313130.71414141414141414141414141414141414141414141414141414141414141414141414141414141414141.51515151515151515151515151515151515151515151515151515151515151515151515151515151515150.71616161616161616161616161616161616161616161616161616161616161616161616161616161616161.61717171717171717171717171717171717171717171717171717171717171717171717171717171717170.61818181818181818181818181818181818181818181818181818181818181818181818181818181818180.31919191919191919191919191919191919191919191919191919191919191919191919191919191919190.72020202020202020202020202020202020202020202020202020202020202020202020202020202020201.52121212121212121212121212121212121212121212121212121212121212121212121212121212121211.02222222222222222222222222222222222222222222222222222222222222222222222222222222222221.02323232323232323232323232323232323232323232323232323232323232323232323232323232323231.22424242424242424242424242424242424242424242424242424242424242424242424242424242424240.22525252525252525252525252525252525252525252525252525252525252525252525252525252525252.02626262626262626262626262626262626262626262626262626262626262626262626262626262626261.22727272727272727272727272727272727272727272727272727272727272727272727272727272727270.72828282828282828282828282828282828282828282828282828282828282828282828282828282828280.32929292929292929292929292929292929292929292929292929292929292929292929292929292929290.33030303030303030303030303030303030303030303030303030303030303030303030303030303030300.43131313131313131313131313131313131313131313131313131313131313131313131313131313131311.53232323232323232323232323232323232323232323232323232323232323232323232323232323232321.43333333333333333333333333333333333333333333333333333333333333333333333333333333333330.33434343434343434343434343434343434343434343434343434343434343434343434343434343434341.43535353535353535353535353535353535353535353535353535353535353535353535353535353535350.23636363636363636363636363636363636363636363636363636363636363636363636363636363636361.23737373737373737373737373737373737373737373737373737373737373737373737373737373737371.23838383838383838383838383838383838383838383838383838383838383838383838383838383838380.53939393939393939393939393939393939393939393939393939393939393939393939393939393939391.54040404040404040404040404040404040404040404040404040404040404040404040404040404040401.74141414141414141414141414141414141414141414141414141414141414141414141414141414141411.74242424242424242424242424242424242424242424242424242424242424242424242424242424242421.14343434343434343434343434343434343434343434343434343434343434343434343434343434343431.34444444444444444444444444444444444444444444444444444444444444444444444444444444444441.74545454545454545454545454545454545454545454545454545454545454545454545454545454545450.94646464646464646464646464646464646464646464646464646464646464646464646464646464646461.14747474747474747474747474747474747474747474747474747474747474747474747474747474747471.34848484848484848484848484848484848484848484848484848484848484848484848484848484848481.44949494949494949494949494949494949494949494949494949494949494949494949494949494949491.95050505050505050505050505050505050505050505050505050505050505050505050505050505050501.75151515151515151515151515151515151515151515151515151515151515151515151515151515151510.45252525252525252525252525252525252525252525252525252525252525252525252525252525252520.05353535353535353535353535353535353535353535353535353535353535353535353535353535353530.15454545454545454545454545454545454545454545454545454545454545454545454545454545454540.25555555555555555555555555555555555555555555555555555555555555555555555555555555555551.15656565656565656565656565656565656565656565656565656565656565656565656565656565656561.75757575757575757575757575757575757575757575757575757575757575757575757575757575757571.75858585858585858585858585858585858585858585858585858585858585858585858585858585858580.95959595959595959595959595959595959595959595959595959595959595959595959595959595959590.36060606060606060606060606060606060606060606060606060606060606060606060606060606060601.06161616161616161616161616161616161616161616161616161616161616161616161616161616161610.56262626262626262626262626262626262626262626262626262626262626262626262626262626262621.56363636363636363636363636363636363636363636363636363636363636363636363636363636363631.36464646464646464646464646464646464646464646464646464646464646464646464646464646464641.46565656565656565656565656565656565656565656565656565656565656565656565656565656565650.36666666666666666666666666666666666666666666666666666666666666666666666666666666666661.36767676767676767676767676767676767676767676767676767676767676767676767676767676767671.36868686868686868686868686868686868686868686868686868686868686868686868686868686868681.16969696969696969696969696969696969696969696969696969696969696969696969696969696969690.67070707070707070707070707070707070707070707070707070707070707070707070707070707070701.6Sheet2

To this:
concat.xlsmABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAP1hdr1hdr2hdr3hdr4hdr5hdr6hdr7hdr8hdr9hdr10hdr11hdr12hdr13hdr14hdr15hdr16hdr17hdr18hdr19hdr20hdr21hdr22hdr23hdr24hdr25hdr26hdr27hdr28hdr29hdr30hdr31hdr32hdr33hdr34hdr35hdr36hdr37hdr38hdr39hdr40hdr41hdr422222222222222222222222222222222222222222221.83555555555555555555555555555555555555555551.24666666666666666666666666666666666666666661.55777777777777777777777777777777777777777771.9610101010101010101010101010101010101010101010101010101010101010101010101010101010101.8712121212121212121212121212121212121212121212121212121212121212121212121212121212121.3814141414141414141414141414141414141414141414141414141414141414141414141414141414141.5916161616161616161616161616161616161616161616161616161616161616161616161616161616161.61020202020202020202020202020202020202020202020202020202020202020202020202020202020201.51122222222222222222222222222222222222222222222222222222222222222222222222222222222221.01223232323232323232323232323232323232323232323232323232323232323232323232323232323231.21325252525252525252525252525252525252525252525252525252525252525252525252525252525252.01426262626262626262626262626262626262626262626262626262626262626262626262626262626261.21531313131313131313131313131313131313131313131313131313131313131313131313131313131311.51632323232323232323232323232323232323232323232323232323232323232323232323232323232321.41734343434343434343434343434343434343434343434343434343434343434343434343434343434341.41836363636363636363636363636363636363636363636363636363636363636363636363636363636361.21937373737373737373737373737373737373737373737373737373737373737373737373737373737371.22039393939393939393939393939393939393939393939393939393939393939393939393939393939391.52140404040404040404040404040404040404040404040404040404040404040404040404040404040401.72241414141414141414141414141414141414141414141414141414141414141414141414141414141411.72342424242424242424242424242424242424242424242424242424242424242424242424242424242421.12443434343434343434343434343434343434343434343434343434343434343434343434343434343431.32544444444444444444444444444444444444444444444444444444444444444444444444444444444441.72646464646464646464646464646464646464646464646464646464646464646464646464646464646461.12747474747474747474747474747474747474747474747474747474747474747474747474747474747471.32848484848484848484848484848484848484848484848484848484848484848484848484848484848481.42949494949494949494949494949494949494949494949494949494949494949494949494949494949491.93050505050505050505050505050505050505050505050505050505050505050505050505050505050501.73155555555555555555555555555555555555555555555555555555555555555555555555555555555551.13256565656565656565656565656565656565656565656565656565656565656565656565656565656561.73357575757575757575757575757575757575757575757575757575757575757575757575757575757571.73462626262626262626262626262626262626262626262626262626262626262626262626262626262621.53563636363636363636363636363636363636363636363636363636363636363636363636363636363631.33664646464646464646464646464646464646464646464646464646464646464646464646464646464641.43766666666666666666666666666666666666666666666666666666666666666666666666666666666661.33867676767676767676767676767676767676767676767676767676767676767676767676767676767671.33968686868686868686868686868686868686868686868686868686868686868686868686868686868681.14070707070707070707070707070707070707070707070707070707070707070707070707070707070701.6Sheet2

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


----------



## cspengel (Dec 29, 2022)

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


----------



## cspengel (Dec 29, 2022)

disregard2NewestMonTest(DONOTUSE)4.xlsmABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAP1QBFLEXFLEXFLEXFLEXQBFLEXFLEXFLEXFLEXComboIDΣ SalaryΣ ProjectionΣ ProbabilityΣ TargetΣ DepthΣ #RestrictionΣ Ceiling ValueΣ ValueΣ StackΣ Stack POSΣ CommasΣ FilterΣ Player1Σ Player2QBFLEXFLEXFLEXFLEXQBFLEXFLEXFLEXFLEXQBFLEXFLEXFLEXFLEXΣ MVP2Justin HerbertJustin HerbertJustin HerbertJustin HerbertZack MossKeenan AllenJustin HerbertDeon JacksonChase McLaughlinIndianapolis Colts664635850065.525153.1860.46531.3006837611.120085INDFLEX,FLEX,FLEX3027.6826.3135.8624.3223.6813.6618.397.3813.027.95WRQBRBKD03Austin EkelerAustin EkelerAustin EkelerAustin EkelerChase McLaughlinMike WilliamsJustin HerbertDeon JacksonChase McLaughlinIndianapolis Colts1311435750064.985156.85559.41631.3970434781.130173INDFLEX,FLEX,FLEX3030.2726.3135.8624.3223.6812.7918.397.3813.027.95WRQBRBKD04Keenan AllenKeenan AllenKeenan AllenKeenan AllenCameron DickerJustin HerbertKeenan AllenDeon JacksonChase McLaughlinIndianapolis Colts110235850069.425152.98560.46531.3006837611.186752INDFLEX,FLEX,FLEX3026.3127.6835.8624.3223.6818.3913.667.3813.027.95QBWRRBKD05Nick FolesNick FolesNick FolesNick FolesGerald EverettJustin HerbertMike WilliamsDeon JacksonChase McLaughlinIndianapolis Colts202635750069.065155.43559.41631.3970434781.20113INDFLEX,FLEX,FLEX3026.3130.2735.8624.3223.6818.3912.797.3813.027.95QBWRRBKD06Mike WilliamsMike WilliamsMike WilliamsMike WilliamsJoshua PalmerMike WilliamsKeenan AllenDeon JacksonChase McLaughlinIndianapolis Colts1403835500057.185157.24554.82631.3336363641.039727INDFLEX,FLEX,FLEX3030.2727.6835.8624.3223.6812.7913.667.3813.027.95WRWRRBKD07Deon JacksonDeon JacksonDeon JacksonDeon JacksonParris CampbellKeenan AllenMike WilliamsDeon JacksonChase McLaughlinIndianapolis Colts849435500057.365156.0254.82631.3336363641.043INDFLEX,FLEX,FLEX3027.6830.2735.8624.3223.6813.6612.797.3813.027.95WRWRRBKD08Michael Pittman Jr.Michael Pittman Jr.Michael Pittman Jr.Michael Pittman Jr.Alec PierceJustin HerbertDeon JacksonChase McLaughlinParris CampbellIndianapolis Colts259335400062.105150.82558.06631.3979629631.150092INDFLEX,FLEX,FLEX,FLEX4026.3135.8624.3225.4123.6818.397.3813.0211.317.95QBRBKWRD09Zack MossZack MossJoshua KelleyJustin HerbertDeon JacksonMichael Pittman Jr.Chase McLaughlinIndianapolis Colts252135800066.925159.40557.54531.311379311.153879INDFLEX,FLEX,FLEX,FLEX4026.3135.8635.1224.3223.6818.397.3810.6613.027.95QBRBWRKD010Chase McLaughlinChase McLaughlinDeAndre CarterKeenan AllenJustin HerbertDeon JacksonParris CampbellIndianapolis Colts665235800061.495154.0258.7631.2953448281.060258INDFLEX,FLEX,FLEX3027.6826.3135.8625.4123.6813.6618.397.3811.317.95WRQBRBWRD011Cameron DickerCameron DickerDonald Parham Jr.Mike WilliamsJustin HerbertDeon JacksonParris CampbellIndianapolis Colts1312035700060.955157.69557.65731.392456141.069385INDFLEX,FLEX,FLEX3030.2726.3135.8625.4123.6812.7918.397.3811.317.95WRQBRBWRD012Gerald EverettGerald EverettJelani WoodsKeenan AllenJustin HerbertDeon JacksonAlec PierceIndianapolis Colts665385700061.115151.4858.17731.2717543861.072192INDFLEX,FLEX,FLEX3027.6826.3135.8622.3123.6813.6618.397.3810.757.95WRQBRBWRD013Joshua PalmerJoshua PalmerLos Angeles ChargersMike WilliamsJustin HerbertDeon JacksonAlec PierceIndianapolis Colts1312185600060.575155.15557.12831.3701785711.081696INDFLEX,FLEX,FLEX3030.2726.3135.8622.3123.6812.7918.397.3810.757.95WRQBRBWRD014Parris CampbellParris CampbellIndianapolis ColtsJustin HerbertKeenan AllenDeon JacksonParris CampbellIndianapolis Colts110835800065.395153.82558.7631.2953448281.1275INDFLEX,FLEX,FLEX3026.3127.6835.8625.4123.6818.3913.667.3811.317.95QBWRRBWRD015Alec PierceAlec PierceAshton DulinJustin HerbertKeenan AllenDeon JacksonAlec PierceIndianapolis Colts110985700065.015151.28558.17731.2717543861.140614INDFLEX,FLEX,FLEX3026.3127.6835.8622.3123.6818.3913.667.3810.757.95QBWRRBWRD016Joshua KelleyJordan WilkinsJustin HerbertMike WilliamsDeon JacksonParris CampbellIndianapolis Colts203235700065.035156.27557.65731.392456141.140964INDFLEX,FLEX,FLEX3026.3130.2735.8625.4123.6818.3912.797.3811.317.95QBWRRBWRD017DeAndre CarterJustin HerbertMike WilliamsDeon JacksonAlec PierceIndianapolis Colts203385600064.655153.73557.12831.3701785711.154553INDFLEX,FLEX,FLEX3026.3130.2735.8622.3123.6818.3912.797.3810.757.95QBWRRBWRD018Donald Parham Jr.Keenan AllenDeon JacksonMichael Pittman Jr.Chase McLaughlinIndianapolis Colts898935550055.225159.9952.95531.2446846850.995045INDFLEX,FLEX,FLEX,FLEX4027.6835.8635.1224.3223.6813.667.3810.6613.027.95WRRBWRKD019Jelani WoodsMike WilliamsDeon JacksonMichael Pittman Jr.Chase McLaughlinIndianapolis Colts1545735450054.685163.66551.9631.3453211011.003394INDFLEX,FLEX,FLEX,FLEX4030.2735.8635.1224.3223.6812.797.3810.6613.027.95WRRBWRKD020Los Angeles ChargersMike WilliamsKeenan AllenDeon JacksonParris CampbellIndianapolis Colts1404435450053.155158.08553.06731.3282568810.975321INDFLEX,FLEX,FLEX3030.2727.6835.8625.4123.6812.7913.667.3811.317.95WRWRRBWRD021Indianapolis ColtsKeenan AllenMike WilliamsDeon JacksonParris CampbellIndianapolis Colts850035450053.335156.8653.06731.3282568810.978623INDFLEX,FLEX,FLEX3027.6830.2735.8625.4123.6813.6612.797.3811.317.95WRWRRBWRD022Ashton DulinMike WilliamsKeenan AllenDeon JacksonMichael Pittman Jr.Indianapolis Colts1403535850057.975166.66552.54631.2471794870.991025INDFLEX,FLEX,FLEX3030.2727.6835.8635.1223.6812.7913.667.3810.667.95WRWRRBWRD023Jordan WilkinsKeenan AllenMike WilliamsDeon JacksonMichael Pittman Jr.Indianapolis Colts849135850058.155165.4452.54631.2471794870.994102INDFLEX,FLEX,FLEX3027.6830.2735.8635.1223.6813.6612.797.3810.667.95WRWRRBWRD124Justin HerbertDeon JacksonMichael Pittman Jr.Parris CampbellIndianapolis Colts252735750062.895160.24555.78631.3060869571.093826INDFLEX,FLEX,FLEX,FLEX4026.3135.8635.1225.4123.6818.397.3810.6611.317.95QBRBWRWRD125Justin HerbertDeon JacksonMichael Pittman Jr.Alec PierceIndianapolis Colts252885650062.515157.70555.25731.2824778761.10646INDFLEX,FLEX,FLEX,FLEX4026.3135.8635.1222.3123.6818.397.3810.6610.757.95QBRBWRWRD126Keenan AllenDeon JacksonMichael Pittman Jr.Parris CampbellIndianapolis Colts899535500051.195160.8351.19631.2385454550.930818INDFLEX,FLEX,FLEX,FLEX4027.6835.8635.1225.4123.6813.667.3810.6611.317.95WRRBWRWRD127Mike WilliamsDeon JacksonMichael Pittman Jr.Parris CampbellIndianapolis Colts1546335400050.655164.50550.14731.340.938055INDFLEX,FLEX,FLEX,FLEX4030.2735.8635.1225.4123.6812.797.3810.6611.317.95WRRBWRWRD128Keenan AllenDeon JacksonMichael Pittman Jr.Alec PierceIndianapolis Colts899685400050.815158.2950.66731.2125925930.941018INDFLEX,FLEX,FLEX,FLEX4027.6835.8635.1222.3123.6813.667.3810.6610.757.95WRRBWRWRD129Keenan AllenAustin EkelerDeon JacksonChase McLaughlinIndianapolis Colts710835900062.375155.8872.97531.3740677971.057203INDFLEX,FLEX,FLEX3027.6829.8635.8624.3223.6813.6630.857.3813.027.95WRRBRBKD130Mike WilliamsAustin EkelerDeon JacksonChase McLaughlinIndianapolis Colts1357635800061.835159.55571.92631.4708620691.06612INDFLEX,FLEX,FLEX3030.2729.8635.8624.3223.6812.7930.857.3813.027.95WRRBRBKD131Austin EkelerKeenan AllenDeon JacksonChase McLaughlinIndianapolis Colts433635900064.7157.03572.97531.3740677971.09661INDFLEX,FLEX,FLEX3029.8627.6835.8624.3223.6830.8513.667.3813.027.95RBWRRBKD132Austin EkelerMike WilliamsDeon JacksonChase McLaughlinIndianapolis Colts526035800064.34159.48571.92631.4708620691.10931INDFLEX,FLEX,FLEX3029.8630.2735.8624.3223.6830.8512.797.3813.027.95RBWRRBKD133Austin EkelerJustin HerbertDeon JacksonAlec PierceIndianapolis Colts341986000068.09154.94575.27731.40751.134833INDFLEX,FLEX,FLEX3029.8626.3135.8622.3123.6830.8518.397.3810.757.95RBQBRBWRD134Justin HerbertAustin EkelerDeon JacksonAlec PierceIndianapolis Colts64786000069.665153.59575.27731.40751.161083INDFLEX,FLEX,FLEX3026.3129.8635.8622.3123.6818.3930.857.3810.757.95QBRBRBWRD135Austin EkelerDeon JacksonChase McLaughlinParris CampbellIndianapolis Colts582735450057.38154.87570.57631.4765137611.052844INDFLEX,FLEX,FLEX,FLEX4029.8635.8624.3225.4123.6830.857.3813.0211.317.95RBRBKWRD136Austin EkelerDeon JacksonMichael Pittman Jr.Chase McLaughlinIndianapolis Colts575535850062.2163.45570.05531.3852991451.063247INDFLEX,FLEX,FLEX,FLEX4029.8635.8635.1224.3223.6830.857.3810.6613.027.95RBRBWRKD137Keenan AllenAustin EkelerDeon JacksonParris CampbellIndianapolis Colts711435850058.345156.7271.21631.3694017090.99735INDFLEX,FLEX,FLEX3027.6829.8635.8625.4123.6813.6630.857.3811.317.95WRRBRBWRD138Mike WilliamsAustin EkelerDeon JacksonParris CampbellIndianapolis Colts1358235750057.805160.39570.16731.4669565221.005304INDFLEX,FLEX,FLEX3030.2729.8635.8625.4123.6812.7930.857.3811.317.95WRRBRBWRD139Keenan AllenAustin EkelerDeon JacksonAlec PierceIndianapolis Colts711585750057.965154.1870.68731.3473043481.008086INDFLEX,FLEX,FLEX3027.6829.8635.8622.3123.6813.6630.857.3810.757.95WRRBRBWRD140Mike WilliamsAustin EkelerDeon JacksonAlec PierceIndianapolis Colts1358385650057.425157.85569.63831.446194691.016371INDFLEX,FLEX,FLEX3030.2729.8635.8622.3123.6812.7930.857.3810.757.95WRRBRBWRD1WorksheetCell FormulasRangeFormulaX2:X40X2=IF($Z$2="",COUNTIF(G2:K2,$Y$2),COUNTIF(G2:K2,$Y$2)*COUNTIF(G2:K2,$Z$2))


----------



## kevin9999 (Dec 29, 2022)

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 (Dec 29, 2022)

kevin9999 said:


> Try this:
> 
> ```
> Option Explicit
> ...


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


----------



## kevin9999 (Dec 29, 2022)

cspengel said:


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


----------

