Delete Row on Criteria - Almost Works

[hip2b2]

I am using the code below to delete a row where any one of a variety of criteria might be met. I expect those who know will find the code to be something of a dog's breakfast as it is a poidge of things I have found on the net.

The issue is that when the text in Col S is marked "Closed" and Col A in the Row immediatly following is not empty both the row marked Closed and the following row are deleted (whereas only the row marked Closed should be deleted)

Dim a As Variant, b As Variant, myVals As Variant, oneVal As Variant
  Dim nc As Long, i As Long, k As Long, lr As Long
  
  Const strVals As String = "Closed|closed"
  
  myVals = Split(strVals, "|")
  nc = Cells.Find(What:="*", LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1
  lr = Range("A" & Rows.Count).End(xlUp).Row
  a = Application.Index(Cells, Evaluate("Row(7:" & lr & ")"), Array(1, 11))
  ReDim b(1 To UBound(a), 1 To 1)
  For i = 2 To UBound(a)
    If Len(a(i, 1)) > 0 Then
      For Each oneVal In myVals
        If InStr(1, a(i - 1, 2), oneVal, vbTextCompare) Then
          b(i, 1) = 1
          k = k + 1
          Exit For
        End If
      Next oneVal
    End If
  Next i
  If k > 0 Then
    With Range("A7").Resize(UBound(a), nc)
      .Columns(nc).Value = b
      .Sort Key1:=.Columns(nc), Order1:=xlAscending, Header:=xlNo
      .Resize(k).EntireRow.Delete
    End With
    End If
'Delete Rows based on criteria
    Set h = Sheets("For acting")
    If h.AutoFilterMode Then h.AutoFilterMode = False
    lr = Columns("A:K").Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    With h.Range("A6:K" & lr)
    
    .AutoFilter Field:=11, Criteria1:=Split("Closed|ABC|ABC Only|XYZ ONLY, Closed|XYZ ONLY; Closed", "|"), Operator:=xlFilterValues
    .Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete
    .AutoFilter Field:=11

Help would be greatly appreciated,

With thanks in advance.

hip

 

[bobaol]

hi hippy, nice to see someone here with over 20 years. wow. long live Excel97 (65,000 rows!!!!) try this... maybe it works. I used the macro recorder to record it, except the error handler part. cheers!

Sub Macro1_all__run__this__to__run__all__macros___()
'''run looking for closed, then run looking for completed
    Application.Run "Macro2Closed"
    Application.Run "Macro3Completed"
End Sub



Sub Macro2Closed()
''' use the error handler, in case of an error, like not finding closed, it goes to cell A1
    On Error GoTo Err_Handler
For i = 1 To 9999
    Application.Goto Reference:="R1C1"
'''go to cell S1
    Application.Goto Reference:="R1C19"
'''select column S
    ActiveCell.Columns("A:A").EntireColumn.Select
'''find closed
    Selection.Find(What:="closed", After:=ActiveCell, LookIn:=xlFormulas2, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
'''move to the next cell, in column T
    ActiveCell.Offset(0, 1).Range("A1").Select
'''delete that entire row
    Selection.EntireRow.Delete
Next
Go_To_A1:
    Application.Goto Reference:="R1C1"
Err_Handler:
    Application.Goto Reference:="R1C1"
End Sub


Sub Macro3Completed()
''' use the error handler, in case of an error, like not finding completed, it goes to cell A1
    On Error GoTo Err_Handler
For i = 1 To 9999
    Application.Goto Reference:="R1C1"
'''go to cell S1
    Application.Goto Reference:="R1C19"
'''select column S
    ActiveCell.Columns("A:A").EntireColumn.Select
'''find completed
    Selection.Find(What:="Completed", After:=ActiveCell, LookIn:=xlFormulas2, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
'''move to the next cell, in column T
    ActiveCell.Offset(0, 1).Range("A1").Select
'''delete that entire row
    Selection.EntireRow.Delete
Next
Go_To_A1:
    Application.Goto Reference:="R1C1"
Err_Handler:
    Application.Goto Reference:="R1C1"
End Sub