How to exclude results from list within another sheet 1

[Rossco82]

Hi All!!

I have the below which does exactly what I need to do with a spreadsheet, however I have one addition to make which I can't quite suss.

To start with, the code -

Sub FilteringCP()

Dim RngOne As Range, cell As Range
Dim LastCell As Long
Dim arrList() As String, lngCnt As Long

With Sheets("List of Accounts")
    LastCell = .Range("M" & Sheets("List of Accounts").Rows.Count).End(xlUp).Row
    Set RngOne = .Range("m2:m" & LastCell)
End With

'load values into an array
lngCnt = 0
For Each cell In RngOne
    ReDim Preserve arrList(lngCnt)
    arrList(lngCnt) = cell.Text
    lngCnt = lngCnt + 1
Next


With Sheets("CP")

    If .FilterMode Then .ShowAllData

    .Range("A1:L1").AutoFilter Field:=6, Criteria1:=arrList, Operator:=xlFilterValues

End With

End Sub

The scenario:
One workbook has 4 tabs/sheets, the fourth tabs column M (sheet name "list of accounts") contains a list of refs that can possibly grow.
We need to allow the above to run, but at the end also filter out anything that is in sheet 4 column M in the current worksheets column F (i.e if column f has a match in m1, m36 (or wherever) this row is then also removed from the current worksheet). After the current ".Range("A1:L1").AutoFilter Field:=6, Criteria1:=arrList, Operator:=xlFilterValues" and before "End With" is where I picture it happening unless someone can suggest better?.

I hope that makes sense?.

Thanks as always!

A wise man once said....
"I think, therefore I yam."
SkipVought 25 Oct 18 12:11

 

[SkipVought]

Hi,

What about an additional column in “the current worksheet“ that is...
[tt]
=ISNA(MATCH(ThisRowLookupValue,’list of accounts’!M:M,1))
[/tt]
The FALSE result should be the ones to remove or include in your filter, I believe.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Rossco82]

Yeah that does the trick. I was just hoping to avoid adding any new columns as not my form. But we'll see if anyone shouts lol.

As always, gracias Skip!

A wise man once said....
"I think, therefore I yam."
SkipVought 25 Oct 18 12:11

 

[SkipVought]

Since you are coding this filter, you can be nonintrusive and oblique by hiding the unwanted additional column from searching eyes.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Rossco82]

Could, but thought sod it. They didn't specify don't change the form it's just my work habit, and so far no one has said a word. So I'm leaving it as is

A wise man once said....
"I think, therefore I yam."
SkipVought 25 Oct 18 12:11

 

[SkipVought]

As the sign above the facsimile machine in the music shop warned:
If it ain’t baroque, don’t fax it!

BTW, just loved your countryman, V.B. for his wit and piano panache.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Rossco82]

Just been to sit with the team using this form/Macro. They absolutely loved it!!!

A wise man once said....
"I think, therefore I yam."
SkipVought 25 Oct 18 12:11

 

[SkipVought]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[strongm]

>Yeah that does the trick

Um … I may be wrong, but I don't think works the way yuop might be thinking. Basically, if your lookup value lies ANYWHERE in between the min and max values in the lookup list, then it'll get matched. You need to change it to

[tt]=ISNA(MATCH(ThisRowLookupValue,’list of accounts’!M:M,0))[/tt]

 

[SkipVought]

My bad. Of course it’s ZERO!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]