Delete rows when specific words and criteria are found

[man]

Hi,

I have a worksheet that contains 2 columns of data. I have listed a sample of the data as below column A and column B

...............Column A....................................Column B
Row 1......blacktalldog.com..........................black tall dog
Row 2......blackhungrymouse.com................black hungry mouse
Row 3......thebigshortdog.net........................the big short dog
Row 4......bigblacktable.net..........................big black table
Row 5......bigtalltree.net...............................big tall tree

I have some questions on how to do the things I want.

1) I want to excel to search the whole document and detect rows with cells that start with the word 'black' and cells that start with the word 'big'. Delete the whole rows.

results to return will be
Row 3......thebigshortdog.net........................the big short dog

2) I want to search Column A only, delete the rows if the cells in Column A ends with '.net'

results to return will be
Row 1......blacktalldog.com..........................black tall dog
Row 2......blackhungrymouse.com................black hungry mouse

3) I want to detect cells that contain the word tall. If the word tall is detected in Column A or Column B, delete the row.

results to return will be
Row 2......blackhungrymouse.com................black hungry mouse
Row 3......thebigshortdog.net........................the big short dog
Row 4......bigblacktable.net..........................big black table


Thanks <!-- / message --> <!-- BEGIN TEMPLATE: ad_showthread_firstpost_sig --> <!-- END TEMPLATE: ad_showthread_firstpost_sig --> <!-- edit note -->

 

[Peter_SSs]

Nothing in this thread relates to a query from an external source. I'm just guessing that your original data in that sheet may have been obtained via such a query. Whether you may want to re-use that query or not is up to you.

 

[man]

Is it possible to have a new sheet called Include Lists (sheet will appear exactly like Exclude Lists). In this Include Lists sheet I will enter the all the words that I want to see. Then after running macro of Include List criteria for the 'Main' worksheet, It will return results in another sheet called 'Include Lists Results'. So In this list, I will be able to see the words I want.

I only thought of this now because sometimes when I browse through the greyed rows, I saw some words that I would like to see instead of becoming greyed. For example in my Exclude Lists, if I input 'tables' in the Before_Dot column, in my Main worksheet, after running the macro, the eight tall tables will be inside the greyed rows.

But I would like to see all rows starting with the word eight. Although the row 'eight tall tables' ends with 'tables' and should be greyed, but it has the starting word 'eight', so I would like to see the row.

I am not sure if my explanation is clear, maybe you have better solutions to this.

 

[Peter_SSs]

Not exactly what you asked, but see if this is any use.

1. Make a new sheet called 'Include Lists' as you suggested, with exactly the same format as the 'Exclude Lists' sheet.

2. The macro I gave you before was called 'man'. If you have not done so already, change its name to, say, 'Exclude'.

3. Copy the whole 'Exclude' macro and Paste it (probably in a new Module would be best).

4. Rename this Pasted version as 'Include'

5. Change this line

Set wsExclude = Sheets("Exclude Lists")

to this

Set wsExclude = Sheets("Include Lists")

Now run the 'Exclude' macro when you want to identify rows containing the excluded words and run the 'Include' macro when you want to identify rows containing the included words.

There would be a better way to do the above, but since I have already put a lot of time into this thread I'm taking the easiest way here.

 

[man]

Not exactly what you asked, but see if this is any use.

1. Make a new sheet called 'Include Lists' as you suggested, with exactly the same format as the 'Exclude Lists' sheet.

Step 1 Done.

2. The macro I gave you before was called 'man'. If you have not done so already, change its name to, say, 'Exclude'.

I right click the 'Main' sheet tab and choose 'View Code'. Then in the VB window left side bar, I double clicked Modules folder, then clicked Module1 (which contains the code), I changed the first row Sub man() to Sub Exclude()

Is this the right way to change the name to 'Exclude'?

3. Copy the whole 'Exclude' macro and Paste it (probably in a new Module would be best).

I right click the 'Main' sheet tab and choose 'View Code'. Then in the VB window use the menus to Insert|Module. A new Module2 appears. I copied and paste the code from Module1 into Module2.

4. Rename this Pasted version as 'Include'

In the Module2 first row, I renamed Sub Exclude() to Sub Include()

5. Change this line

Set wsExclude = Sheets("Exclude Lists")

to this

Set wsExclude = Sheets("Include Lists")

Now run the 'Exclude' macro when you want to identify rows containing the excluded words and run the 'Include' macro when you want to identify rows containing the included words.

There would be a better way to do the above, but since I have already put a lot of time into this thread I'm taking the easiest way here.

Step 5 Done.

I emptied all the cells in 'Include Lists'. Under the Start column, I input the word 'eight'. On the Main sheet, I run the the include macro by going Tools|Macro|Macros...|select the Include macro then Run.

The results that returned is these 2 rows

Heading1..............Heading2.........Start...End...Anywhere...Before_Dot...Selected
bigblacktable.net...big black table..1..................................................1
bigtalltree.net.......big tall tree......1..................................................1

I tested other columns in the Include Lists, the results is not accurate too. For example if I put the word 'dog' under the Anywhere column, it does not produce the right result too.

Do check if I have done my steps correctly. Thanks.

 

[Peter_SSs]

It sounds like you have done everything correctly. The problem (at least one of them) is that I missed another change that is required in the code. In both macros, change this line

ActiveWorkbook.Names.Add Name:=aExclHead(1, i), RefersToR1C1:= _
    "='Exclude Lists'!R2C" & i & ":R" & lr & "C" & i

to this

ActiveWorkbook.Names.Add Name:=aExclHead(1, i), RefersToR1C1:= _
    "='" & wsExclude.Name & "'!R2C" & i & ":R" & lr & "C" & i

 

[man]

It is working now. Thanks.