Delete rows using variable key words

Deliverable7

New Member
Joined
Apr 9, 2016
Messages
33
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

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

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,384
Office Version
  1. 365
Platform
  1. Windows
How about
Code:
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,975
Office Version
  1. 365
Platform
  1. Windows
You can store all the values in an array and loop through them, i.e.
Code:
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.
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,975
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

You are welcome.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,722
Office Version
  1. 2010
Platform
  1. Windows
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.
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,975
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,505
Messages
5,602,062
Members
414,498
Latest member
jordanmiller7890

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