Delete Row on Criteria - Almost Works

hip2b2

Board Regular
Joined
May 5, 2003
Messages
109
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)

VBA Code:
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
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

bobaol

Board Regular
Joined
Jun 3, 2002
Messages
117
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!


VBA Code:
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
 
Last edited by a moderator:

Watch MrExcel Video

Forum statistics

Threads
1,123,351
Messages
5,601,132
Members
414,429
Latest member
Bilaal xaka

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top