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

 

[Peter_SSs]

Try

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:=Split("X|Y|Z", "|"), Operator:=xlFilterValues
Rows("7:" & u).Delete
If h.AutoFilterMode Then h.AutoFilterMode = False

If you have a very large data set (tens of thousands of rows or more) post back as there would be a faster way for that size data.

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[hip2b2]

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:=Split("X|Y|Z", "|"), Operator:=xlFilterValues Rows("7:" & u).Delete If h.AutoFilterMode Then h.AutoFilterMode = False

Peter,

I hope you are old enough to know that when I say your solution is the "The Cat's ***" you know that is high compliment. You only had to modify one line of code and Voila!!

My wife seeing that you were able to provide exactly what I asked for, now asks for something else. She is wondering if the code could be tweaked a little more so that if the field contain X Y or Z then the row will be deleted. She is concerned that a slight misspelling of the actual terms will cause the row to be missed and not deleted.

Many thanks

hip

 

[Peter_SSs]

I hope you are old enough to know that when I say your solution is the "The Cat's ***" you know that is high compliment. You only had to modify one line of code and Voila!!

Cheers. Glad it worked for you.

wondering if the code could be tweaked a little more so that if the field contain X Y or Z then the row will be deleted.

No, not really a tweak as filtering for 'contains' is limited to two values. Still, this is an opportunity to see the 'fast code' that I mentioned before. Whilst the code is considerably longer, the execution speed, especially over large data, is much better.

Note that, like autofilter, this code is not case-sensitive so rows containg "X" or "x" will be deleted.

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Sub Del_Contains()
  Dim a As Variant, b As Variant, myVals As Variant, oneVal As Variant
  Dim nc As Long, i As Long, k 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
  a = Range("K7", Range("K" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To UBound(a), 1 To 1)
  For i = 1 To UBound(a)
    For Each oneVal In myVals
      If InStr(1, a(i, 1), oneVal, vbTextCompare) Then
        b(i, 1) = 1
        k = k + 1
        Exit For
      End If
    Next oneVal
  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]

Cheers. Glad it worked for you.



No, not really a tweak as filtering for 'contains' is limited to two values. Still, this is an opportunity to see the 'fast code' that I mentioned before. Whilst the code is considerably longer, the execution speed, especially over large data, is much better.

Note that, like autofilter, this code is not case-sensitive so rows containg "X" or "x" will be deleted.

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Sub Del_Contains()
  Dim a As Variant, b As Variant, myVals As Variant, oneVal As Variant
  Dim nc As Long, i As Long, k 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
  a = Range("K7", Range("K" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To UBound(a), 1 To 1)
  For i = 1 To UBound(a)
    For Each oneVal In myVals
      If InStr(1, a(i, 1), oneVal, vbTextCompare) Then
        b(i, 1) = 1
        k = k + 1
        Exit For
      End If
    Next oneVal
  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

Many thanks, both worked for me

 

[Peter_SSs]

You're welcome.

 

[hip2b2]

And another thing....

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:=Split("X|Y|Z", "|"), Operator:=xlFilterValues
Rows("7:" & u).Delete
If h.AutoFilterMode Then h.AutoFilterMode = False

Is working perfectly.

I now find that I would like to delete rows where Column B contains "Q"
The only way I have been able to do this is by running the origonal code a second time

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:="Q"
   'Rows("7:" & u).Delete Shift:=xlUp
   'If h.AutoFilterMode Then h.AutoFilterMode = False

Just wondering if ther is a more elegant way to do this?

Thanks

 

[Peter_SSs]

I now find that I would like to delete rows where Column B contains "Q"
The only way I have been able to do this is by running the origonal code a second time

Is this a completely separate requirement (deleting rows where col B contains "Q") or is it somehow to be combined with one of the other requirements?
And do you mean col B contains "Q" or col B equals "Q"?

Which "original code" are you referring to? The code from post #1, the last code in post #& that is all commented out except one line, something else?

 

[hip2b2]

Sorry for the lousy description

This is the code I am running. I want to delete rows where Col K contains "X", "Y", or "Z"; or Col B contains "Q". Though the code works perfectly), it just seems inelegant to repeat the code as I have done. I won't be sorry to be told this is a reasonable way to accomplish my goal, I just get a sense there is a better way.
.

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:=Split("X|Y|Z", "|"), Operator:=xlFilterValues
Rows("7:" & u).Delete
If h.AutoFilterMode Then h.AutoFilterMode = False
   
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:=2, Criteria1:="Q"
Rows("7:" & u).Delete Shift:=xlUp
If h.AutoFilterMode Then h.AutoFilterMode = False

Thanks in advance.

hip

 

[Peter_SSs]

I want to delete rows where Col K contains "X", "Y", or "Z"; or Col B contains "Q".

Thanks, understand now. You could switch to Advanced Filter to do this with a single Filter/Delete but I would probably still stick with Autofilter. You don't need to do the whole process twice - try something like this.

Set h = Sheets("ABC")
Application.ScreenUpdating = False
If h.AutoFilterMode Then h.AutoFilterMode = False
lr = Columns("A:K").Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
With h.Range("A6:K" & lr)
  .AutoFilter Field:=11, Criteria1:=Split("X|Y|Z", "|"), Operator:=xlFilterValues
  .Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete
  .AutoFilter Field:=11
  .AutoFilter Field:=2, Criteria1:="*Q*"
  .Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete
End With
h.AutoFilterMode = False
Application.ScreenUpdating = True