×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

How to exclude results from list within another sheet

How to exclude results from list within another sheet

How to exclude results from list within another sheet

(OP)
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 -


CODE --> VBA

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

RE: How to exclude results from list within another sheet

Hi,

What about an additional column in “the current worksheet“ that is...

=ISNA(MATCH(ThisRowLookupValue,’list of accounts’!M:M,1))

The FALSE result should be the ones to remove or include in your filter, I believe.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: How to exclude results from list within another sheet

(OP)
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

RE: How to exclude results from list within another sheet

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: How to exclude results from list within another sheet

(OP)
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 2thumbsup

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

RE: How to exclude results from list within another sheet

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: How to exclude results from list within another sheet

(OP)
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

RE: How to exclude results from list within another sheet

👍

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: How to exclude results from list within another sheet

>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

=ISNA(MATCH(ThisRowLookupValue,’list of accounts’!M:M,0))

RE: How to exclude results from list within another sheet

My bad. Of course it’s ZERO!

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close