Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 233
  • Last Modified:

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
0
Jagwarman
Asked:
Jagwarman
  • 4
  • 2
1 Solution
 
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:
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
 
Harry LeeCommented:
Jagwarman, forgot to ask, is the data all in one column or there are multiple columns?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now