If values not present, delete rows contain values

[BustedAvi]

Hi all,

I'm a very noob VBA writer and I've done my best to figure this out, but to no avail.

I have a list of words in column A. I need a macro that will search column A for five different words. If all five are present, I need 2 rows containing specific text (also in column A) to be deleted.

I appreciate the help.

 

[evnoort]

wrong post pls ignor

 

[BustedAvi]

Hey Peter,

I have a further application for your code - but I can't figure out how to reorganize it.

I need to do the exact same thing, except for a different set of words as criteria and delete different rows if the criteria aren't met. Note this process would follow the previous one you defined for me.

In summary, if

Array("E. Coli", "Total Coliforms", "Arsenic", "Manganese", _
"Fluoride", "Nitrate", "Nitrite")

are absent, then

Array("DWQI Health", "Health Rating")

need to be deleted.

Regards,

BustedAvi

For the future you will get many more helpers if you can post a copyable screen shot directly in your post. Most helpers here don't much like typing out test data.
Check my signature block for some options.

Try this in copy of your workbook.

You could add/remove words with both lists by adjusting the arrays near the top of the code.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> CheckVals()<br>****<SPAN style="color:#00007F">Dim</SPAN> ChkWords, DelWords<br>****<SPAN style="color:#00007F">Dim</SPAN> bStop <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN><br>****<SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>****<SPAN style="color:#00007F">Dim</SPAN> Found <SPAN style="color:#00007F">As</SPAN> Range<br>****<br>****ChkWords = Array("E. Coli", "Arsenic", "Manganese", "Fluoride", _<br>********"Nitrate", "Total Coliforms", "Nitrite")<br>****DelWords = Array("DWQI Aesthetic", "Aesthetic Rating")<br>****<br>****i = <SPAN style="color:#00007F">LBound</SPAN>(ChkWords)<br>****<SPAN style="color:#00007F">With</SPAN> Range("A4", Range("A" & Rows.Count).End(xlUp))<br>********<SPAN style="color:#00007F">Do</SPAN><br>************<SPAN style="color:#00007F">Set</SPAN> Found = .Find(What:=ChkWords(i), LookIn:=xlValues, _<br>****************LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)<br>************<SPAN style="color:#00007F">If</SPAN> Found <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>****************bStop = <SPAN style="color:#00007F">True</SPAN><br>************<SPAN style="color:#00007F">Else</SPAN><br>****************i = i + 1<br>************<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>********<SPAN style="color:#00007F">Loop</SPAN> <SPAN style="color:#00007F">While</SPAN> <SPAN style="color:#00007F">Not</SPAN> bStop And i <= <SPAN style="color:#00007F">UBound</SPAN>(ChkWords)<br>********<SPAN style="color:#00007F">If</SPAN> bStop <SPAN style="color:#00007F">Then</SPAN><br>************<SPAN style="color:#00007F">For</SPAN> i = <SPAN style="color:#00007F">LBound</SPAN>(DelWords) <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(DelWords)<br>****************<SPAN style="color:#00007F">Set</SPAN> Found = .Find(What:=DelWords(i), LookIn:=xlValues, _<br>********************LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)<br>****************<SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Found <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>********************Found.EntireRow.Delete<br>****************<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>************<SPAN style="color:#00007F">Next</SPAN> i<br>********<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>****<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[Peter_SSs]

If you are trying to do just the same thing but with different sets of words, then just change the ChkWords array and the DelWords array near the start of the code. Did you try that?

 

[BustedAvi]

I did actually - I get an error:
"Duplicate declaration in current scope"

I'm trying to do the same thing except twice in a row, once for a set of words then again for a different set.

 

[Peter_SSs]

I did actually - I get an error:
"Duplicate declaration in current scope"

I'm trying to do the same thing except twice in a row, once for a set of words then again for a different set.

If you have copied the whole code then changed the array values, you will also need to change the name of one of the macros - you cannot have two with the same name. So just change the blue part to something else.
Sub CheckVals()