Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2014-02-13
7
Medium Priority
?
230 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 52

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 2000 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

719 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