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

 

[man]

Hmm, sounds a bit contradictory.



If you followed the sample as suggested, there were two values (big, black) under Start. In my final screen shot after running the code five rows are marked in the Start column - 3 that started with black and 2 that started with big. Did you get different results than me with the same sample data?

I did get the same results as seen in your final screen shot as seen in the post #28, the only difference is the row 7 to 16 arrangment of the rows are a bit different.

Can detecting more than one word work as well? For example, if the eighttalltables.com may become eightt all tables / eightt alltables / eight talltables , I will need to detect words starting with eightt , words ending with talltables, all tables, alltables.

Yes, my mistake. I thought I had tested that but must not have. The Anywhere column in the Exclude Lists sheet still needs to keep its heading, even if there is no other data below that heading.

Put another Dim statement with the others at the top of the code

Dim bListExists As Boolean

Replace the section of code in the middle with this:

For i = 1 To lExclCategories
lr = wsExclude.Cells(Rows.Count, i).End(xlUp).Row
bListExists = (lr > 1)
On Error Resume Next
ActiveWorkbook.Names(aExclHead(1, i)).Delete
On Error GoTo 0
If bListExists Then
ActiveWorkbook.Names.Add Name:=aExclHead(1, i), RefersToR1C1:= _
"='Exclude Lists'!R2C" & i & ":R" & lr & "C" & i
With rMain.Offset(, 1 + i)
.Formula = sFormulas(i - 1)
.Value = .Value
End With
End If
Next i

Ok, after adding the codes, now the Anywhere column can be empty

 

[Peter_SSs]

I did get the same results as seen in your final screen shot as seen in the post #28, the only difference is the row 7 to 16 arrangment of the rows are a bit different.

Yes that could have happened since I actually did quite a bit of testing with different words etc so my sheet was sorted many times in many different ways so it may have ended up getting sorted a bit differently to yours. I presume that doesn't matter as long as the selected rows are at the bottom of the sheet.

Can detecting more than one word work as well? For example, if the eighttalltables.com may become eightt all tables / eightt alltables / eight talltables , I will need to detect words starting with eightt , words ending with talltables, all tables, alltables.

Not exactly sure what you mean here. Try it.

Note however, that if you have
'eightt all tables'
in column B and 'eight' in the 'Start' or 'Anywhere' Exclude List, the row will still be 'Selected'. To avoid that is a considerably more onerous task and I think getting beyond what you could expect from a free public forum like this. At least I think I am unlikely to attack another significant enhancement to the code. That's not to say somebody else will not step in if more is required. Who knows? You can always ask and see.

If there are minor adjustments to my code required then I would still look at those.

Ok, after adding the codes, now the Anywhere column can be empty

Great.

 

[man]

Yes that could have happened since I actually did quite a bit of testing with different words etc so my sheet was sorted many times in many different ways so it may have ended up getting sorted a bit differently to yours. I presume that doesn't matter as long as the selected rows are at the bottom of the sheet.

It doesn't matter as long as the selected rows are at the bottom of the sheet

Not exactly sure what you mean here. Try it.

Note however, that if you have
'eightt all tables'
in column B and 'eight' in the 'Start' or 'Anywhere' Exclude List, the row will still be 'Selected'. To avoid that is a considerably more onerous task and I think getting beyond what you could expect from a free public forum like this. At least I think I am unlikely to attack another significant enhancement to the code. That's not to say somebody else will not step in if more is required. Who knows? You can always ask and see.

If there are minor adjustments to my code required then I would still look at those.

Great.

Using the same sample as in #post 13,

For example, now I cleared all the cells that are in the Exclude Lists tab. (All cells are empty now).

In Exclude Lists, under the Start column, I input the word 'eight'. I then go to the Main worksheet and run the macro. The macro works fine and detect the row that contains eighttalltables.com eight tall tables.

Now I empty all the cells in Exclude List again, then under the Start column, if i input any of these words in the cell (one word at one time only, the Start column will only have one cell filled)

eightt
eighttall
eight t
eight tall
eightt all
eighttalltables
eight tall tables

I then go to the Main worksheet and run the macro. The row that contains eighttalltables.com eight tall tables will not be detected.

The same problem occurs with the Exclude Lists, Before_Dot column.

I cleared all the cells that are in the Exclude Lists tab. (All cells are empty now).

In Exclude Lists, under the Before_Dot column, I input the word 'tables'. I then go to the Main worksheet and run the macro. The macro works fine and detect the row that contains eighttalltables.com eight tall tables.

Now I empty all the cells in the Exclude List again, then under the Before_Dot column, if i input any of these words in the cell (one word at one time only, the Before_Dot column will only have one cell filled)

alltables
all tables
talltables
tall tables
eighttalltables
eight tall tables

I then go to the Main worksheet and run the macro. The row that contains eighttalltables.com eight tall tables will not be detected.

 

[Peter_SSs]

I have not had much time to look at, test or consider the most efficient way to do this and I'm about to be away for a few days. However, try replacing this section. Column B is no longer looked at by the formulas/code.

<font face=Courier New>    sFormulas = Array( _<br>        "=IF(ISNUMBER(LOOKUP(1,SEARCH(" & aExclHead(1, 1) & ",A1))),1,"""")", _<br>        "=IF(ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH(" & aExclHead(1, 2) & ",A1))),1,"""")", _<br>        "=IF(ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH(" & aExclHead(1, 3) & ",A1))),1,"""")", _<br>        "=IF(ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH(" & aExclHead(1, 4) & "&""."",A1))),1,"""")")</FONT>

 

[man]

I have not had much time to look at, test or consider the most efficient way to do this and I'm about to be away for a few days. However, try replacing this section. Column B is no longer looked at by the formulas/code.

sFormulas = Array( _
"=IF(ISNUMBER(LOOKUP(1,SEARCH(" & aExclHead(1, 1) & ",A1))),1,"""")", _
"=IF(ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH(" & aExclHead(1, 2) & ",A1))),1,"""")", _
"=IF(ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH(" & aExclHead(1, 3) & ",A1))),1,"""")", _
"=IF(ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH(" & aExclHead(1, 4) & "&""."",A1))),1,"""")")

I replaced the code and it works better, the words without spaces can be detected, but the words with spaces cannot be detected

These words can be detected:

Start
eightt
eighttall
eighttalltables

Before_Dot
alltables
talltables
eighttalltables

These words cannot be detected

Start
eight t
eight tall
eightt all
eight tall tables

Before_Dot
all tables
tall tables
eight tall tables

One question, is it better when column B is no longer looked by the formula or is it better when column B is looked by the formula? Is there any "side effects" when column B is not looked by the formula?

 

[Peter_SSs]

This now looks at both columns (A & B). Does it produce the results you expect?

<font face=Courier New>sFormulas = Array( _<br>    "=IF(ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH(""|""&" & aExclHead(1, 1) & ",""|""&A1&""|""&B1))),1,"""")", _<br>    "=IF(ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH(" & aExclHead(1, 2) & ",A1&""|""&B1))),1,"""")", _<br>    "=IF(ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH(" & aExclHead(1, 3) & ",A1))),1,"""")", _<br>    "=IF(ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH(" & aExclHead(1, 4) & "&""."",A1&""|""&B1&"".""))),1,"""")")</FONT>

 

[man]

This now looks at both columns (A & B). Does it produce the results you expect?

sFormulas = Array( _
"=IF(ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH(""|""&" & aExclHead(1, 1) & ",""|""&A1&""|""&B1))),1,"""")", _
"=IF(ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH(" & aExclHead(1, 2) & ",A1&""|""&B1))),1,"""")", _
"=IF(ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH(" & aExclHead(1, 3) & ",A1))),1,"""")", _
"=IF(ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH(" & aExclHead(1, 4) & "&""."",A1&""|""&B1&"".""))),1,"""")")

I tried this formula with with all the words in post #35. Only 1 word is not detected.

Start
eightt all

All other words are detected.

 

[Peter_SSs]

I tried this formula with with all the words in post #35. Only 1 word is not detected.

Start
eightt all

That would be because 'eightt all' does not appear at the start of any row in column A or column B in the sample data from post #13.

 

[man]

Ok I understand now. Thanks.

 

[man]

I got a question, usually I will delete the data in 'Main' then copy and paste new data into the 'Main' worksheet for the data to be analysed

I will select all (Ctrl +A) at the 'Main' worksheet and press delete to delete all. Then this box pops up.

"The range you deleted is associated with a query that retrieves data from an external source. Do you want to delete the query in addition to the range? If you click No, the query will retrieve new data to the worksheet the next time the query is refreshed. Yes No "

Should I click Yes or No?