Filter by a Selected List

[jomili]

I have a good start on a macro I'm developing (code at bottom). What it currently does is to filter the hardcoded range by the list I select. Good so far. What I want to add, and am not sure how to do, is:
1) Allow the filtering of the Activesheet by a list on another sheet.
2) Allow me to select the range on my activesheet to filter. For instance, if my table lives in c10:H99999 I don't want to apply a filter at A1.

Not sure how to plumb all that into my code, so coming here for help. Thanks in advance.

Please Login or Register  to view this content.

[jaslake]

Hi jomili

Change this to the Code Name of the Sheet you wish to Filter...

Please Login or Register  to view this content.

Create a Named Range of this list (make it Dynamic)...call it whatever you wish...

a list on another sheet.

In the Code change this to your Named Range...

Please Login or Register  to view this content.

In the Code Set the Range & Column to be Filtered

Please Login or Register  to view this content.

Complete Code...

Please Login or Register  to view this content.

[jomili]

jaslake,

I apologize if my description of the project wasn't descriptive enough. I appreciate your efforts, but it's not in line with my ultimate goal here. The idea is a repeatable macro where I can autofilter a selected column range (which would be in a different place, different size each time) by a list that may or may not be in the same worksheet or workbook. So, ideally I'd need two prompts, one to pick the Data, including the column to filter, and the other to pick the list to filter by. So, in my original code, I DIMMED SRC and Targ as worksheets, but didn't use them. My thought is SRC would be the list to use (to filter by), and Targ would be the datatable to filter, then we'd have to narrow in on the column to filter.

[xlnitwit]

Hi,

Perhaps this?

Please Login or Register  to view this content.

[jomili]

Awesome! that's spot on, exactly what I was trying to do. I've tested it with filters on, with filters off, and with a table, and it performs flawlessly so far. Thank you. I'll be using this one often.

In case you're interested, the reason I wanted to develop this is because I have Jeff Weir's code for filtering a pivot from http://dailydoseofexcel.com/archives...xternal-range/ and I use it every day. So, when I go to a regular data table and want to filter it, often it's by a list I've saved in another workbook, so by habit I try to do the right-click and filter by the list, but Jeff's code only works for Pivot tables. Now thanks to you I have the code to do it for any table/Range.

My next step is how to plumb this into my right-click menu, so off I go in search of a solution to that.

Thanks, God bless you, and Merry Christmas!

[xlnitwit]

You can adapt his AddRightClick routine but use

Please Login or Register  to view this content.

[jomili]

FYI that I changed the Filter macro as shown below to use the CurrentRegion and the Activecell. In this way, I click into the table I want to filter. CurrentRegion defines the parameters of my data source, and the active column is the one I want to filter. So the only InputBox needed is for the list. So far seems to be working fine.

Please Login or Register  to view this content.

[jomili]

just realized I never put the full code for this project, so I've put it all below:

Please Login or Register  to view this content.