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

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
Try

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:=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

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

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

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

Board Regular
Joined
May 5, 2003
Messages
117

ADVERTISEMENT

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’)

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

hip2b2

Board Regular
Joined
May 5, 2003
Messages
117

ADVERTISEMENT

And another thing....

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:=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
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:="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

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

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

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

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

Watch MrExcel Video

Forum statistics

Threads
1,128,089
Messages
5,628,609
Members
416,327
Latest member
Chimay

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