Deleting rows if column contains...

[Foz1980]

Hi all,

My VBA skills are very limited. I have found the below code from another thread which I've copied and pasted into my coding, it has worked for me to a point.
This currently deletes any rows where column T contains the phrase "Pending Failure". But I need to go a step further and it delete any rows where column T contains either "Pending Failure" or "Variation".

    Dim MyCol As String
    Dim i As Integer
    For i = 1 To Range("T" & "65536").End(xlUp).Row Step 1
    If Application.WorksheetFunction.CountIf(Range("A" & i & ":AZ" & i), "Pending Failure") > 0 Then
    Range("T" & i).EntireRow.Delete
    End If
    Next i

Please can someone help?

Cheers

Chris

 

[sheetspread]

Try:

Dim MyCol As String
    Dim i As Integer
    For i = 1 To Range("T" & "65536").End(xlUp).Row Step 1
    If application.sum(Application.CountIf(Range("A" & i & ":AZ" & i), array("Pending Failure","Variation"))) > 0 Then
    Range("T" & i).EntireRow.Delete
    End If
    Next i

 

[Fluff]

Another option

Sub FltrDel()

   With ActiveSheet
      If .AutoFilterMode Then .AutoFilterMode = False
      .Range("T:T").AutoFilter 1, "*Pending Failure*", xlOr, "*Variation*"
      .UsedRange.Offset(1).SpecialCells(xlVisible).EntireRow.Delete
      .AutoFilterMode = False
   End With
      
End Sub

 

[Foz1980]

Great thank-you, both examples have worked perfectly

 

[Fluff]

Glad we could help & thanks for the feedback

 

[Foz1980]

Hi all,

I have used the following code and it deletes all but one row that contains "Pending Failure" - can anyone tell me where I have gone wrong?

    Dim MyCol As String
    Dim i As Integer
    For i = 1 To Range("T" & "65536").End(xlUp).Row Step 1
    If Application.Sum(Application.CountIf(Range("A" & i & ":AZ" & i), Array("Pending Failure", "Variation"))) > 0 Then
    Range("T" & i).EntireRow.Delete
    End If
    Next i

Cheers

Chris

 

[sheetspread]

Do they match exactly (no extra spaces, etc)? Test by typing =A1="search term" in an empty cell.

 

[Fluff]

When deleting rows in a loop you should always loop from bottom to top.
try

For i = Range("T" & Rows.Count).End(xlUp).Row To 2 Step -1

 

[Foz1980]

When deleting rows in a loop you should always loop from bottom to top.
try

For i = Range("T" & Rows.Count).End(xlUp).Row To 2 Step -1

Legend! This has worked perfectly, thanks alot Fluff.

 

[Fluff]

You're welcome