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

hip2b2

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

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

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

hip2b2

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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,397
Office Version
  1. 365
Platform
  1. Windows
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?

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

Board Regular
Joined
May 5, 2003
Messages
117
Like this?

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

Watch MrExcel Video

Forum statistics

Threads
1,127,331
Messages
5,624,063
Members
416,010
Latest member
NJT

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