How can I simplify this?

[vba4PTV]

Hi,
New to vba and using it to make macros. I built a macro based off one I found online. It's designed to delete rows if they have certain criteria, but each criteria is in separate auto filter section. Is there a way to list all of the criteria(TV show names) in one "section" ?

Sub DeleteRowsF()
With ActiveSheet
.AutoFilterMode = False
With Range("f1", Range("f" & Rows.Count).End(xlUp))
.AutoFilter 1, "*Big Bang Theory*"
On Error Resume Next
.Offset(1).SpecialCells(12).EntireRow.Delete
End With
.AutoFilterMode = False


.AutoFilterMode = False
With Range("f1", Range("f" & Rows.Count).End(xlUp))
.AutoFilter 1, "*Modern Family*"
On Error Resume Next
.Offset(1).SpecialCells(12).EntireRow.Delete
End With
.AutoFilterMode = False


.AutoFilterMode = False
With Range("f1", Range("f" & Rows.Count).End(xlUp))
.AutoFilter 1, "*Vanderpump Rules*"
On Error Resume Next
.Offset(1).SpecialCells(12).EntireRow.Delete
End With
.AutoFilterMode = False


End With
End Sub

 

[DanteAmor]

Hi @vba4PTV .

Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.​

Try this:

Sub DeleteRows()
  Dim arr As Variant, itm As Variant
  
  arr = Array("Big Bang Theory", "Modern Family", "Vanderpump Rules")
  For Each itm In arr
    Columns("F").Replace what:="*" & itm & "*", replacement:="#N/A", Lookat:=xlWhole
  Next
  On Error Resume Next
  Range("F1", Range("F" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------

 

[vba4PTV]

Thank you for the warm welcome Dante! This worked perfect after I added a lot more names. In mine I used The asterisks around the show names to delete them when the name comes in a little different, like "Modern Family X-Mas Special" instead of just "Modern Family". Will this still work for those?

 

[DanteAmor]

"Modern Family X-Mas Special" instead of just "Modern Family". Will this still work for those?

Of course.
Works for "Big Band Theory 1", "Big Band Theory 2", "Big Band Theory 3", or "New Big Band Theory 1", etc.
The asterisks are in this part: what:="*" & itm & "*"

Such is this world of macros and programming . Everything must be tested and if something does not work we correct it.

 

[vba4PTV]

Awesome - much appreciated!