I am looking for VBA to delete rows if they contain certain words

I am looking for VBA to delete rows if they contain certain words but I can only find code that is to delete a single word.

I want to delete rows if they contain either Grp1 or CAT or Eq or Grp3 or 371
Can an expert assist please

Thanks
JagwarmanAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Harry LeeConnect With a Mentor Commented:
I think this may work better.

Sub DeleteRowsContains()
    Cells.Replace What:="*CAT*", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="*Grp1*", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="*Grp3*", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="*Eq*", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="*371*", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Columns("A:A").Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.EntireRow.Delete Shift:=xlUp
    Range("A1").Select
End Sub

Open in new window

0
 
Rgonzo1971Commented:
Hi,

If the word is the entire value of the cell and is only in a column, you could try this

Sub Macro1()
Dim lstRw As Long
Dim Idx As Long
Dim c As Range

lstRw = Range("A" & Rows.Count).End(xlUp).Row
For Idx = lstRw To 2 Step -1
    Set c = Range("A" & Idx)
    If c.Value = "Grp13" Or c.Value = "CAT" Or _
             c.Value = "Eq" Or c.Value = "Grp3" Or c.Value = "371" Then
        c.EntireRow.Delete
    End If
Next
End Sub

Open in new window

Regards
0
 
Harry LeeCommented:
Jagwarman, forgot to ask, is the data all in one column or there are multiple columns?
0
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
JagwarmanAuthor Commented:
Grp1 or CAT or Eq or Grp3 or 371 will always be in column A

Regards
0
 
Harry LeeCommented:
In this case, the vba I have attached will do the job.
0
 
JagwarmanAuthor Commented:
Thanks for this it does exactly what I was looking for
0
 
Harry LeeCommented:
That's great!

I used to do it like what Rgonzo1971 suggested, using a reverse loop to delete the lines; however, I find that it sometimes doesn't do a clean job living some of the Need to be deleted like behind.

So, I rather have a long VBA code but have the job done properly.

Glad I can help.
0
All Courses

From novice to tech pro — start learning today.