Delete If Met

[billandrew]

Evening All

Wondering if someone could lead me to an alternative. The below Loop deletes the row if a cell condition (Text "Test") is located in Column J, also looking to delete the 3 rows above. Coincidentally found a similar request on the forum yesterday, however unsure how to amend.

Thank You All

Sub DeleteIfMet()

Dim lr As Long: lr = Range("J" & Rows.Count).End(xlUp).Row

For i = 2 To lr
    If Cells(i, "J") = "Test" Then
Cells(i, "J").EntireRow.Delete
Cells(i, "J").Offset(-1).EntireRow.Delete
Cells(i, "J").Offset(-2).EntireRow.Delete
Cells(i, "J").Offset(-3).EntireRow.Delete
    End If
Next
End Sub

 

[Peter_SSs]

Always best to work from the bottom up when deleting rows like that.
Also always best to declare all variables. You have declared lr as Long but not declared i at all
Note too, that your code, & therefore mine below, is case-sensitive so would not delete if the value was "test" or "TEST" or "TeSt"
Try this

Sub DeleteIfMet()
  Dim lr As Long, i As Long
  
  lr = Range("J" & Rows.Count).End(xlUp).Row
  For i = lr To 2 Step -1
      If Cells(i, "J").Value = "Test" Then
        Rows(i - 3).Resize(4).Delete
        i = i - 3
      End If
  Next i
End Sub

 

[billandrew]

Thank You sir

Could you elaborate the concept of

Rows(i - 3).Resize(4).Delete
        i = i - 3

 

[billandrew]

I believe I have the answer.

The variable i = the text found. i -3 goes up 3 rows while the resize method selects the 4 rows and then deletes all applicable.

 

[Peter_SSs]

I believe I have the answer.

The variable i = the text found. i -3 goes up 3 rows while the resize method selects the 4 rows and then deletes all applicable.

Correct. That is, delete all 4 rows at once rather than 4 separate deletions.

And then, since the row you were checking and the 3 above have just been deleted use the i = i - 3 to save checking 3 rows that have already been checked since they just moved up 4 rows from below.

 

[billandrew]

Thank You once again - much appreciated!

 

[billandrew]

That where I originally looped from 2 to lr instead of the proper method 2 to lr.

Learning Once Again....