Delete rows using variable key words

[Deliverable7]

Hi Guys.
Can someone please help me to consolidate/simply the following code. I have a need to delete a number of rows in a large sheet that contain variable keywords (10) within a range of text strings in column A. Each key word only appears in the cells of column a once, the remaining text in the cell is variable.
I have a work around in that i run the Subs below through a call function, it works but is irritating. I know there is a 'better way'.
Thanks

Sub DeleteRowsWithPROJECT()
  Columns("A").Replace "*PROJECT*", "#N/A", xlWhole, , False, , False, False
  On Error Resume Next
  Columns("A").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
  On Error GoTo 0
End Sub


Sub DeleteRowsWithNF()
  Columns("A").Replace "*NF*", "#N/A", xlWhole, , False, , False, False
  On Error Resume Next
  Columns("A").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
  On Error GoTo 0
End Sub


Sub DeleteRowsWithCLIENT()
  Columns("A").Replace "*CLIENT*", "#N/A", xlWhole, , False, , False, False
  On Error Resume Next
  Columns("A").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
  On Error GoTo 0
End Sub


Sub DeleteRowsWithNoofSAMPLES()
  Columns("A").Replace "*# of SAMPLES*", "#N/A", xlWhole, , False, , False, False
  On Error Resume Next
  Columns("A").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
  On Error GoTo 0
End Sub

 

[Fluff]

How about

Sub Deliverable7()
   Dim Ary As Variant
   Dim i As Long
   
   Ary = Array("*project*", "*nf*", "*client*")
   For i = 0 To UBound(Ary)
      Range("A:A").Replace Ary(i), "#N/A", xlWhole, , False, , False, False
   Next i
   On Error Resume Next
   Range("A:A").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
   On Error GoTo 0
End Sub

 

[Joe4]

You can store all the values in an array and loop through them, i.e.

Sub DeleteAll()

    Dim vars As Variant
    Dim i As Long
    
    Application.ScreenUpdating = False
    
'   Store all variables to replace in an array
    vars = Array("*PROJECT*", "*NF*", "*CLIENT*", "*# of SAMPLES*")
    
'   Loop through the array and do all replacements
    For i = LBound(vars) To UBound(vars)
        Columns("A").Replace vars(i), "#N/A", xlWhole, , False, , False, False
        On Error Resume Next
        Columns("A").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
        On Error GoTo 0
    Next i
    
    Application.ScreenUpdating = True
    
End Sub

EDIT: Looks like Fluff beat me to the punch!
Only real difference is the Application.ScreenUpdating rows, which will suppress the screen flickering when running (and if you have a lot of data, might run a little faster.

 

[Deliverable7]

Thanks a lot guys, works a treat.

 

[Joe4]

You are welcome.

 

[Rick Rothstein]

EDIT: Looks like Fluff beat me to the punch!
Only real difference is the Application.ScreenUpdating rows, which will suppress the screen flickering when running (and if you have a lot of data, might run a little faster.

Actually, your codes are more different than that. Your code makes the replacement and then uses SpecialCells to delete the row immediately within your For..Next loop. Since you are doing repeated deletes, your code needs the ScreenUpdating code lines to manage the flicker. Fluff's code, on the other hand, uses the loop to make the replacements only, then, after the loop finishes, uses SpecialCells to do all of the deletes in one fell swoop. Because of that, there is no screen flickering in Fluff's code and, hence, no need for for it to use the ScreenUpdating code lines your code needed.

 

[Joe4]

Actually, your codes are more different than that. Your code makes the replacement and then uses SpecialCells to delete the row immediately within your For..Next loop. Since you are doing repeated deletes, your code needs the ScreenUpdating code lines to manage the flicker. Fluff's code, on the other hand, uses the loop to make the replacements only, then, after the loop finishes, uses SpecialCells to do all of the deletes in one fell swoop. Because of that, there is no screen flickering in Fluff's code and, hence, no need for for it to use the ScreenUpdating code lines your code needed.

Good to know.
Thanks for the info.