Find Values from Sheet2 on Sheet 1 and Hide Those Rows

[silverbluemoon]

So I admit, this is probably way easier than I've made it out to be, but considering I've spent most of two days trying to figure this out and have yet to do so, I have arrived here, desperately hoping someone here will take pity on me and help me out.

• I have two sheets in a file called MyFile.
• Sheet1 has a full table of data in named range "myNamedRange" - an array that spans Sheet 1 from A2:K322.
• Sheet2 has a list of data in Column A (Range A2:A85) that I need to find in Sheet1 - either directly by the column the data is most likely to be found in [in this case, Column C ( specifically Range C2:C22)] -OR- via the named range array.

GOAL: Use the list of values in Sheet2 to hide all rows on Sheet1 that match.

Is there any way to do this? VBA is acceptable. Filters are acceptable. I just apparently don't know how to make either do what I need. I apologize in advance for the newbie question.

Please let me know if you need more information.

Thanks for helping!

 

[Jerry Sullivan]

Hi silverbluemoon,

A relatively easy formula-based approach would be to add a helper column to Sheet1 that indentifies the matches, then use AutoFilters to hide those rows.

For example, place in K2, then copy down...
=MATCH(C2,Sheet2!A$2:A$85,0)
then filter to show only #N/A

Or for a nicer display, place in K2, then copy down...
=IF(ISNUMBER(MATCH(C2,Sheet2!A$2:A$85,0)),"Hide","Show")
then filter to show only "Show"

For a VBA approach, there are some examples in this thread that could be adapted...
http://www.mrexcel.com/forum/excel-questions/697963-excluding-items-out-filter-macro.html

 

[silverbluemoon]

[SOLVED] Re: Find Values from Sheet2 on Sheet 1 and Hide Those Rows

Hi silverbluemoon,

A relatively easy formula-based approach would be to add a helper column to Sheet1 that indentifies the matches, then use AutoFilters to hide those rows.

For example, place in K2, then copy down...
=MATCH(C2,Sheet2!A$2:A$85,0)
then filter to show only #N/A

Or for a nicer display, place in K2, then copy down...
=IF(ISNUMBER(MATCH(C2,Sheet2!A$2:A$85,0)),"Hide","Show")
then filter to show only "Show"

For a VBA approach, there are some examples in this thread that could be adapted...
http://www.mrexcel.com/forum/excel-questions/697963-excluding-items-out-filter-macro.html

Jerry, you're a lifesaver! This worked perfectly. Thank you!!