Delete entire rows modify?

[samilynn]

I am using this code to delete all rows that contain "x" in Column O.
Can I modify it to delete rows that contain X, Y or Z in that column?

Thank you!

Sub DeleteRowIfCellX()

Const MyTarget = "x"

Dim Rng As Range, DelCol As New Collection, x
Dim i As Long, j As Long, k As Long

' Calc last row number
j = Range("O" & Rows.Count).End(xlUp).Row

' Collect rows range with MyTarget
For i = 1 To j
If WorksheetFunction.CountIf(Rows(i), MyTarget) > 0 Then
k = k + 1
If k = 1 Then
Set Rng = Rows(i)
Else
Set Rng = Union(Rng, Rows(i))
If k >= 100 Then
DelCol.Add Rng
k = 0
End If
End If
End If
Next
If k > 0 Then DelCol.Add Rng

' Delete rows with MyTarget
For Each x In DelCol
x.Delete
Next

' Update UsedRange
With ActiveSheet.UsedRange: End With

' Restore screen updating and events
Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

 

[Nine Zero]

Change this line

If WorksheetFunction.CountIfs(Rows(i), MyTarget) > 0 Then

with this

If WorksheetFunction.CountIfs(Rows(i), "x") > 0 Or WorksheetFunction.CountIfs(Rows(i), "y") > 0 Or WorksheetFunction.CountIfs(Rows(i), "z") > 0 Then

 

[Fluff]

Another option

Sub delRws()
   Dim Ary As Variant
   Dim i As Long
   
   Ary = Array("X", "Y", "Z")
   For i = 0 To UBound(Ary)
      Range("O:O").Replace Ary(i), "=XXX", xlWhole, , False, , False, False
   Next i
   Range("O:O").SpecialCells(xlFormulas, xlErrors).EntireRow.Delete
End Sub

 

[Nine Zero]

@Fluff this is much cleaner I just personally have not been able to wrap my head around Arrays in VBA ... Java and Ruby are my main languages and they are easy for me to grasp.... time to study VBA Arrays

 

[Nine Zero]

Change this line

If WorksheetFunction.CountIf(Rows(i), MyTarget) > 0 Then

with this

If WorksheetFunction.CountIf(Rows(i), "x") > 0 Or WorksheetFunction.CountIf(Rows(i), "y") > 0 Or WorksheetFunction.CountIf(Rows(i), "z") > 0 Then

EDIT: REALIZED I ACCIDENTALLY PUT AN "s" IN THE COUNTIF..... REMOVED IN THIS