VBA to delete entire row if column P contains certain text

[AEL8871]

I have a sheet containing data in A1:CF5364. This will change each time I run this report, there may be more or fewer rows/columns.

I'd like to be able to delete an entire row if column P contains one of 9 key words. In this case, the cell would be an exact match but I don't want to limit this VBA for other uses. I'd like to delete the row only if it contains (not equals) the key word.

Key words:
apple
orange
kiwi
banana
mango
grape
strawberry
blueberry
tomato

 

[mumps]

Try:

Sub DeleteRows()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim myArray As Variant
    myArray = Array("apple", "orange", "kiwi", "banana", "mango", "grape", "strawberry", "blueberry", "tomato")
    Range("P1:P" & LastRow).AutoFilter Field:=1, Criteria1:=myArray, Operator:=xlFilterValues
    Range("P2:P" & LastRow).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    Range("P1").AutoFilter
    Application.ScreenUpdating = True
End Sub

This will work with exact matches only.

 

[Fluff]

How about

Sub Delrws()
   Dim ary As Variant
   Dim i As Long
   
   ary = Array("apple", "orange", "kiwi", "banana")
   With Range("P:P")
      For i = 0 To UBound(ary)
         .Replace "*" & ary(i) & "*", "", xlPart, , False, , False, False
      Next i
      .SpecialCells(xlBlanks).EntireRow.Delete
   End With
End Sub

This assumes that you wont have any blank cells in col P

 

[AEL8871]

How about

Sub Delrws()
   Dim ary As Variant
   Dim i As Long
  
   ary = Array("apple", "orange", "kiwi", "banana")
   With Range("P:P")
      For i = 0 To UBound(ary)
         .Replace "*" & ary(i) & "*", "", xlPart, , False, , False, False
      Next i
      .SpecialCells(xlBlanks).EntireRow.Delete
   End With
End Sub

This assumes that you wont have any blank cells in col P

Is there a way to adjust this code to include a search for column I, J, L for example. I need to delete rows based on a yes/no value in these columns. In addition there is a column where I will need to delete all rows that include a blank in column K.

 

[Joe4]

Is there a way to adjust this code to include a search for column I, J, L for example. I need to delete rows based on a yes/no value in these columns. In addition there is a column where I will need to delete all rows that include a blank in column K.

That is a significantly different question that the original one posted.

Due to the fact that it is a different question, and the original was posted 3 years ago, it would be a far better idea to post your question to a brand new thread instead of an old one. Then it will appear as a new unanswered question that will show up on "Unanswered threads" listing that many people use to look for questions that need help.

 

[AEL8871]

Thank you! This has been reposted at the link below.

VBA to delete entire row based on certain filters

I have a bunch of filters I'd like to automate on a spreadsheet. All based on a Yes/No/Blank cell value For example: If Column N contains "No" in any row, delete the entire row. If Column J contains "Yes" in any row, delete the entire row. If Column K contains blanks in any row, delete the...

www.mrexcel.com