VBA code to delete rows where a cell value is X or Y , or Z

[hip2b2]

I am using the following code to delete rows in the ABC sheet where the citeria is currently X.

I need the code to be modified to allow multiple variables (X, Y or Z)

Set h = Sheets("ABC")
    'If h.AutoFilterMode Then h.AutoFilterMode = False
    'u = h.Range("K" & Rows.Count).End(xlUp).Row
    'h.Range("A6:K200" & u).AutoFilter Field:=11, Criteria1:="X"
    'Rows("7:" & u).Delete Shift:=xlUp
    'If h.AutoFilterMode Then h.AutoFilterMode = False

As always any help is appreciated

regards

hip

 

[hip2b2]

I knew there had to be a better way.

Thanks

hip

 

[hip2b2]

One last itteration (I thought I could do this myself from the code previously provided, but well I can't).

i would like to:
Delete rows where Col A is not empty AND Col K (in the row immedatly above) contains X or Y or Z

Merry Christmas and Happy New Year to all

hip

 

[Peter_SSs]

i would like to:
Delete rows where Col A is not empty AND Col K (in the row immedatly above) contains X or Y or Z

Like this?

Sub Del_Contains_v2()
  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 = "X|Y|Z" '<- Edit this to include all the 'contains' items that you want
  
  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
    Application.ScreenUpdating = False
    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
    Application.ScreenUpdating = True
  End If
End Sub

 

[hip2b2]

Like this?

Sub Del_Contains_v2()
  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 = "X|Y|Z" '<- Edit this to include all the 'contains' items that you want
 
  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
    Application.ScreenUpdating = False
    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
    Application.ScreenUpdating = True
  End If
End Sub

Perfect.

Thanks 10^6, and Merry Christmas

 

[Peter_SSs]

Perfect.

Thanks 10^6, and Merry Christmas

Cheers. Same to you.