delete rows not containing certain text

ewso · #1 03-16-2017, 10:14 PM

i need a script that will delete every row that doesn't have certain words or phrases that I specify. how could i do this? thanks

ewso · #2 03-17-2017, 08:14 PM

nobody knows how to do this?

Logit · #3 03-18-2017, 09:17 AM

Code:

Option Explicit

Sub DeleteRowNoInclude()
'Update20140618
Dim xRow As Range
Dim rng As Range
Dim WorkRng As Range
Dim xStr As String
Dim i As Integer
On Error Resume Next
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", WorkRng.Address, Type:=8)
xStr = Application.InputBox("Text", Type:=2)
Application.ScreenUpdating = False
For i = WorkRng.Rows.Count To 1 Step -1
    Set xRow = WorkRng.Rows(i)
    Set rng = xRow.Find(xStr, LookIn:=xlValues)
        
    If Not rng Is Nothing Then
       xRow.Delete
    End If
Next
Application.ScreenUpdating = True
End Sub

ewso · #4 03-18-2017, 11:23 AM

thanks logit, i found that same code from extendoffice, but that doesn't really work for me, since i have multiple words/phrases that i need to use as my criteria. that code seems to be ok if you only have one word to use as a criteria. i'm looking for something more like this, but for rows instead of cells:

https://www.thespreadsheetguru.com/t...word-or-phrase

Logit · #5 03-18-2017, 11:56 AM

The macro will do what you are seeking. Remove the word Not from the
macro line as indicated, then run the code.

The two Input Boxes that appear give you much more range to do what you want
without having to constantly go into the macro and change the code for the range or
search term you are needing.

Code:

Option Explicit

Sub DeleteRowNoInclude()
'Update20140618
Dim xRow As Range
Dim rng As Range
Dim WorkRng As Range
Dim xStr As String
Dim i As Integer
On Error Resume Next
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", WorkRng.Address, Type:=8)
xStr = Application.InputBox("Text", Type:=2)
Application.ScreenUpdating = False
For i = WorkRng.Rows.Count To 1 Step -1
    Set xRow = WorkRng.Rows(i)
    Set rng = xRow.Find(xStr, LookIn:=xlValues)
        
    If rng Is Nothing Then   '<-- I removed Not from the line. Try it now.
       xRow.Delete
    End If
Next
Application.ScreenUpdating = True
End Sub

NoSparks · #6 03-19-2017, 08:32 AM

@ ewso

You've indicated the need of a 'script' to do what you want, but have given ZERO other information.

Expecting a 'works for me' macro without providing a sample worksheet indicative of what you're working with is unrealistic.

You say "doesn't have certain words or phrases that I specify."
Then in response to Logit's suggestion you say "i have multiple words/phrases that i need to use as my criteria"
which seems to indicate "that I specify" is more likely "have in a list", but you're not saying that.

Would you care to post a sample workbook so we're all on the same page and know what is being dealt with?

Logit · #7 03-19-2017, 08:44 AM

Hey NoSparks. Good to 'see' you again.

ewso:

If you have a listing of words and want to scan the sheet for all words on the list at one time,
look at this:

http://stackoverflow.com/questions/1...fined-in-range

ewso · #8 03-19-2017, 11:01 AM

logit, it doesn't work for me because i have multiple words that i have to test for and having to do a check against one criteria at a time will remove rows i don't want removed. for example, if i want to remove all rows not containing the word 'one,' 'two,' or 'three,' and i run the code the first time against the word 'one,' it will remove all rows that don't contain the word 'one,' but will also remove the rows that contain the words 'two' and 'three,' which are rows i want to keep. i modified the script from the web page i provided earlier to include multiple criteria to check against, but again, that only removes cells, not rows. if that script can be modified to remove rows instead of cells, it would be perfect.

Logit · #9 03-19-2017, 11:10 AM

AS NoSparks indicated, post your existing project so it can be reviewed.

Remove any confidential information.

Indicate a 'before' and 'after' simulation of what you are expecting.

Without the benefit of seeing your workbook, this will only go in circles.

ewso · #10 03-19-2017, 11:43 AM

Quote:

Originally Posted by Logit

AS NoSparks indicated, post your existing project so it can be reviewed.

Remove any confidential information.

Indicate a 'before' and 'after' simulation of what you are expecting.

Without the benefit of seeing your workbook, this will only go in circles.

i haven't started the project yet. i have to figure out how to do this before i can start. is something not clear about what i'm trying to do? if the script from the webpage i provided earlier can just be altered to remove rows instead of cells, that's really all i need.

Logit · #11 03-19-2017, 12:12 PM

The URL link you provided shows how to search for ONE WORD at a time, in a specified area/range of the worksheet and delete the row that contains that word. My first post does the same thing.

My first post: deletes all rows containing the word you are searching for.

My second post: deletes all rows EXCEPT those that contain the word you are searching for.

The URL link http://stackoverflow.com/questions/1...fined-in-range will search multiple words at the same time and delete any row that contains any of those words.

Either you don't understand how the macro works ... or you are not clearly explaining what it is you are seeking.

In either case, you now have access to three different methods:

You have already been given several examples that will do what you need. You only need to choose one.

NoSparks · #12 03-19-2017, 12:34 PM

Quote:

if the script from the webpage i provided earlier can just be altered to remove rows instead of cells, that's really all i need.

What you've pointed out in post #8 pretty much contradicts that, but here you go

Code:

Sub RowDoesNotContain()

Dim x As Long, ContainWord As String

'What phrase do you want to test for?
  ContainWord = "Magic"

With Sheets("Sheet1")    '<~~~ change as necessary
    For x = .Cells(Rows.Count, "A").End(xlUp).Row To 1 Step -1
        If Not Rows(x).Find(ContainWord) Is Nothing Then Rows(x).Delete
    Next x
End With

End Sub

ewso · #13 03-19-2017, 01:31 PM

Quote:

Originally Posted by NoSparks

What you've pointed out in post #8 pretty much contradicts that, but here you go

Code:

Sub RowDoesNotContain()

Dim x As Long, ContainWord As String

'What phrase do you want to test for?
  ContainWord = "Magic"

With Sheets("Sheet1")    '<~~~ change as necessary
    For x = .Cells(Rows.Count, "A").End(xlUp).Row To 1 Step -1
        If Not Rows(x).Find(ContainWord) Is Nothing Then Rows(x).Delete
    Next x
End With

End Sub

thanks nosparks, but this is removing the rows that contain the specified text instead of removing the rows which do not contain the specified text.

edit: i removed the word 'Not' in the code and now it works fine.

thanks logit and nosparks.

ewso · #14 12-27-2017, 07:53 PM

I thought that this was working but it isn't. Is there a way to have more than one ContainWord and only have the sub delete a row if none of the ContainWords are found? I tried editing the sub to add another ContainWord, but it ends up deleting every row on the page. It searches for the first ContainWord and deletes all the rows that don't contain that word, and then it searches for the second ContainWord and deletes all the rows that don't contain that word, leaving me with a blank page. So if I have a spreadsheet and I want to remove all rows that don't contain either the words coffee or cups, can I do this?

NoSparks · #15 12-27-2017, 10:13 PM

Yes, pretty sure you can do that.
This would be a real good time to re-read post #6.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Macro to delete rows not containing specific text	Alimou	Excel Programming	4	02-06-2017 07:35 AM
Delete blank rows between the two rows that contain data	beginner	Excel Programming	5	12-26-2014 12:29 AM
Delete All empty Rows - Print - Undo all Rows deleted	Bathroth	Word VBA	1	10-01-2014 01:40 PM
Delete Blank Rows (Cyrillic Text in Spreadsheet )	dozd	Excel	1	02-22-2013 03:24 AM
Delete all rows but the last.	elky1967	Word VBA	14	09-21-2012 05:27 AM