Help with Advance Filtering using VBA

[GreenTee]

Hello,

I'm trying to figure out a way to do some advance filtering using VBA(as the title suggests ) . Basically.. I'm trying to filter a table from a dropdown list in a different cell. Normally this would be fairly simple however, the way the table is set up is the values in the drop down are actual headers in the table. So, I want to filter the table for any cells that have VALUES for that column header that matches what's selected in the drop down.

I've attached an example of what I have so far. Perhaps someone could take a look and let me know if I'm even going in the right direction or if I need to learn something completely different. Please advise.

Thanks in advance!
Example.xlsm

[Norie]

All you need to do is put the value from the validation into the criteria as a header.

[GreenTee]

Sounds legit. Sorry but I'm still super noob with VBA so I tried referencing some of my old work that has something similar to what I believe you're describing.

I re-wrote the old code to something like this...

Please Login or Register  to view this content.

Unfortunately though... this doesn't work. I turned the validation into a table called "Location" so I could try and do the above. I probably didnt' need to do that.. but I don't know of another way without having to turn it into a table.(which didn't work anyway)

any ideas?

Thanks.

[Norie]

Instead of referencing the cell with validation take the value from it and set up the criteria range elsewhere or add it to the existing criteria range.

[GreenTee]

I think I get it.

So I'm still referencing the validation cell via A2. However, I'm doing an Indirect. Let me see if I can explain this...(i'm terrible at explanations)...but I created a named range for cell A2 called "Criteria". The formula for that named range is "=Indirect($A$2". A few columns to the right I created 3 columns one for each country America, Mexico, Japan all with an X underneath it for referencing.

then I changed the vba to reference the named range "Criteria"

for all intensive purposes it works the way I want it to.. but only half way. After the initial selection from the drop down.. it DOES filter for that indirect however, it re-writes the formula for the named ranged "Criteria" to whatever cells that Indirect is referring to.

Here's the sample workbook again with what I did... You can change the dropdown to any country and you can see how it only works the first time.

Example.xlsm

[Norie]

Nothing seems to happen when I selected something in the data validation list apart from E2 getting selected.

What's supposed to happen?

This is more the sort of thing I was thinking.

Please Login or Register  to view this content.

[GreenTee]

Wow that is Awesome! So simple yet it does amazing things! This is what I was looking for.

Couple of follow-up questions.

1. How do I get it to reset if I left A2 blank?

2. I'm trying to make it work with cell C2 so is it possible to do multiple criteria range in the advanced filter while keeping them separate? Meaning If I just had a change in C2...would I be able to just filter for only what's in C2 and not look at what's in cell A2?

[GreenTee]

I figured it ou!!

Thanks for all your help sir!!