• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1104
  • Last Modified:

Excel 2007 "delete rows containing text"

I have a large spreadsheet with 4-5,000 rows. I want to delete rows that contain certain words or exact phrases automatically. How would I do that in Excel 2007?

I actually went out searching for an add-in that might do that but didn't find one.

Thanks for your help.

David
0
drperdew
Asked:
drperdew
  • 5
  • 4
  • 3
  • +1
1 Solution
 
syeager305Commented:
This shoudl work... replace your prhases in the array fPhrase...


Sub ReplacePhrases()
'
' '''use this macro to replace bad Phrases
On Error Resume Next
counter = 0

'''put the phrases your looking for here
fPhrase = Array("Phrase", "Phrase1", "Phrase2")
[A1].Activate
Do Until counter = UBound(fPhrase)
On Error GoTo nextfPhrase:
    Cells.Find(What:=fPhrase, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
        rAdd = ActiveCell.Address
            Do Until lAdd = rAdd
                Cells.FindNext(After:=ActiveCell).Activate
                lAdd = ActiveCell.Address
                r = ActiveCell.Row
                Rows(r).Select
                Selection.Delete
            Loop
nextfPhrase:
counter = counter + 1
Loop


End Sub


0
 
patrickabCommented:
drperdew,

Please upload a sample file.

Patrick
0
 
zorvek (Kevin Jones)ConsultantCommented:
You can do what you want with the AutoFilter function. Select the table and click Filter on the Data tab. Filter the table to show only those rows you want to delete. Select the visible rows and delete them. Continue until all of the rows you want deleted are deleted. To filter in a partial string, use the "Text Filters" options in the filter drop down menu.

Kevin
0
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!

 
syeager305Commented:
Small correction...


Sub ReplacePhrases()
'
' '''use this macro to replace bad Phrases
On Error Resume Next
counter = 0

'''put the phrases your looking for here
fPhrase = Array("Phrase", "Phrase1", "Phrase2")

Do Until counter = UBound(fPhrase)
On Error GoTo nextfPhrase:
[A1].Activate
    Cells.Find(What:=fPhrase, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
        rAdd = ActiveCell.Address
            Do Until lAdd = rAdd
                Cells.FindNext(After:=ActiveCell).Activate
                lAdd = ActiveCell.Address
                r = ActiveCell.Row
                Rows(r).Select
                Selection.Delete
            Loop
nextfPhrase:
counter = counter + 1
Loop


End Sub
0
 
drperdewAuthor Commented:
syeager305: would you mind putting this in a spreadsheet and showing me what you mean. I tried to add the code and it did not work for me - thanks.
0
 
zorvek (Kevin Jones)ConsultantCommented:
Did you try using the AutoFilter function? You will be able to do what you want without VBA code.

Kevin
0
 
syeager305Commented:
Sorry, I didn't thoroughly test that before I sent it... here the NEW code is with along with the code spreadsheet.
HNY!

Regards,


Sub ReplacePhrases()
'
' '''use this macro to replace bad Phrases
On Error Resume Next
counter = 0

'''put the phrases your looking for here
fPhrase = Array("Phrase", "Phrase1")

Do Until counter = UBound(fPhrase) + 1
On Error GoTo nextfPhrase:
[A1].Select
    Set c = Cells.Find(fPhrase(counter), After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False)
    If c Is Nothing Then GoTo nextfPhrase:

    Cells.Find(What:=fPhrase(counter), After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
        rAdd = ActiveCell.Address
       
            Do Until lAdd = rAdd
                Cells.FindNext(After:=ActiveCell).Activate
                lAdd = ActiveCell.Address
                r = ActiveCell.Row
                Rows(r).Select
                Selection.Delete
            Loop
           
nextfPhrase:
counter = counter + 1
Loop


End Sub


Replace-Phrase.xls
0
 
drperdewAuthor Commented:
Kevin -

This was a huge file. I really wanted a way to automatically look for duplicates and delete those after the first instance. Auto filter would do it, but it would take a long time and be manual.
0
 
drperdewAuthor Commented:
syeager305:

I'm assuming that I have to know what I'm looking for and input it in the array to find and delete. Not really what I was looking for... See the post above. Appreciate the effort.

Seems like there should be a simple de-duper add-in out there somewhere? Surely, I'm not the first person to want this...

thanks.
0
 
zorvek (Kevin Jones)ConsultantCommented:
How big is the file? I can guarantee you that any third party doohicky or VBA code will be slower than the built in auto filter function. Trust me on that one ;-)

Another perspective? The time you have spent posting this question and working it could have been spent doing the auto filter approach and you would probably be done by now.

Have you used the auto filter function before?

Kevin
0
 
drperdewAuthor Commented:
Yes - the autofilter is actually the way I solved the problem. But I was hoping to find an automated solution. And yes, I've used the autofilter a lot. Still isn't what I wanted here though.
0
 
zorvek (Kevin Jones)ConsultantCommented:
The problem we faced with this request is that, no matter how we slice the solution, you are going to have to type in the text strings you want to search for and delete. Imagine the most streamlined scenario: an automated routine that you run, it queries you for the string, and then does the deletion. We could have provided that for you but you still would have had to run and enter the text string for each text string to be sought and deleted. Using the auto filter is not much different in terms of the types and quantities of actions you have to perform to complete the task. Enter the filter parameter, select, delete. Repeat until done.

Kevin
0
 
drperdewAuthor Commented:
Got it. Thanks Kevin. Appreciate the thoughtfulness.
dp
0

Featured Post

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!

  • 5
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now