Creating a Dynamic Criteria Range for Advanced Filter Macro

[cypressknees]

Greetings. First timer here.

After searching high and low, I'm having no luck figuring out how to create a macro (that I could assign to a button, say) that will check a range for advanced filtering criteria before producing the filtered results in a new location.

The situation is such that sometimes the criteria range involves OR situations necessitating multiple rows be included in the criteria range.

For instance, sometimes a user would want to filter just on entries that occur in one state (e.g., NY) and sometimes they might need to look at two or more states (e.g., NY or NJ) in addition to including other variables. I feel that there must be some way to define a dynamic range that avoids having blank rows when autofiltering.

Thank you so much for your insight and I'm happy to try and anything I can.


-cypress

 

[jim may]

Try this one line of code:

MyCriteriaRng = Activesheet.range("Criteria").address

 

[cypressknees]

First off, thank you so much for your response. As I'm relatively new to VBA, I might be missing the elegant part of the code you provided.

I've included the advanced filter code I generated via macro recorder below. As it is currently, I tried creating a table (using excel 2010) that contains my criteria headers plus up to five subsequent rows for filter criteria. When I simply use one row for a criterion the output is still every data entry I have. I assume this is because it's recognizing the entire table and not simply the rows that contain data.

I'm not entirely clear on how the code you furnished fits into the whole.

Many thanks.

Sub AdvancedFilter1()
'
' AdvancedFilter1 Macro
' Using a dynamic criteria range to advanced filter
'


'
    Range("A7:AV506").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
        Range("CriteriaFilter"), Unique:=False
End Sub

 

[jim may]

You are assigning a Named Range to your Criteria Range above.

You asked for away to get the criteria address;

In anew line after your only line enter:

MyCriteriaRng = Range("CriteriaFilter").address

 

[mikerickson]

Have you looked at the .CurrentRegion property? If the criteria range has a blank column on each side of it, you could use
CriteriaRange:= Range("CriteriaFilter").Cells(1,1).CurrentRegion

 

[cypressknees]

Thank both of you gentlemen for your input. In then end I've simply developed a workaround that uses different buttons according to how many rows of criteria will be needed.

Not the most elegant by a long shot, but it should suit my purposes.

Again, thank you for taking the time to help me out.


Cheers,
cypressknees