Excel 2007 "delete rows containing text"

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

drperdew,

Please upload a sample file.

Patrick

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

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

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.

Did you try using the AutoFilter function? You will be able to do what you want without VBA code.

Kevin

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

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.

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.

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.

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

Got it. Thanks Kevin. Appreciate the thoughtfulness.
dp