match entire cell content and delete row

**hluk** · 06-20-2009, 01:03 PM

Hi!

So I have a separate sheet (called "negative") with thousands of phrases I do not want in my "data" sheet. I want to filter through column C in "data" sheet for any phrases that matches entire cell content of any items in column A of the "negative" sheet.

The filter and delete row works, except I don't know for sure if it is matching THE ENTIRE CELL CONTENT before deleting the row. It seems to be deleting when the cell contents are close to what I have on my list in my "negative" sheet.

Thanks in advance!

Please Login or Register  to view this content.

**shg** · 06-20-2009, 05:39 PM

Stop the code before doing the deletion and look at the filter.

**hluk** · 06-29-2009, 07:27 PM

if i stop the code, then it will no longer serve as an automatic filter and delete code...

**martindwilson** · 06-29-2009, 07:35 PM

i think shg is saying, temporarily change the code so it only works up to the set autofilter, then look at it to see what is showing,just to debug it.

**Phil_V** · 06-30-2009, 04:09 AM

Use the F8 Key to step the debugger through your code then you can see what it is doing along each step of the way... (or from the 'Debug' menu of the VBA window).

One thing I would be tempted to do would be this:

[code] 'Apply the filter
.Range("C1:C" & .Rows.Count).AutoFilter Field:=1, Criteria1:="=" & cell.Value[code]

It is also worth noting that if cell.value contains a wildcard it will be used in the filter, for example, if cell.value="A* Pass" then the filter will match entries of "A* Pass", but also;
"A rubbish Pass"
"A good Pass"
"Arabian Pass"

If you do have wildcards present in the cell.value then you might have to do some kind of escape sequence around them before using them as the input to your filter.