Solved

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

Posted on 2014-02-13
7
225 Views
Last Modified: 2014-02-17
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
Comment
Question by:Jagwarman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 51

Expert Comment

by:Rgonzo1971
ID: 39856683
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
 
LVL 12

Accepted Solution

by:
Harry Lee earned 500 total points
ID: 39856789
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
 
LVL 12

Expert Comment

by:Harry Lee
ID: 39856889
Jagwarman, forgot to ask, is the data all in one column or there are multiple columns?
0
Industry Leaders: 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!

 

Author Comment

by:Jagwarman
ID: 39857221
Grp1 or CAT or Eq or Grp3 or 371 will always be in column A

Regards
0
 
LVL 12

Expert Comment

by:Harry Lee
ID: 39857248
In this case, the vba I have attached will do the job.
0
 

Author Closing Comment

by:Jagwarman
ID: 39864097
Thanks for this it does exactly what I was looking for
0
 
LVL 12

Expert Comment

by:Harry Lee
ID: 39865350
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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question