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.

 

[cspengel]

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]

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]

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]

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]

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]

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

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]

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]

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]

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.