Changing range in Dynamic Filter

[sveinhelge.urdal]

I need to filter my listings in Excel to show only elements with due date the next two weeks. I find next week and next month in the dynamic filtering.

Is it possible to change that to next two weeks (or 14 days)? How can I manage to change that?

 

[Luke M]

All of the quick choice settings you see can actually be created using the custom AutoFilter. To see this in use, first select "Filter next week", and then go to Filter - Custom Filter. You'll see the setting that XL automatically filled in for you. If you want the next two weeks, you can use the Custom Autofilter and just change the "is before" date to the appropriate date.


Alternatively, if you add a helper column with a formula like this:

=AND(A2>=TODAY(),A2<TODAY()+14)

You could just always filter that column for TRUE to see things with a due date in next 2 weeks.

 

[sveinhelge.urdal]

Thanks to Luke M for a good answer.


Is it possible to make a button with a macro ore something that turns on that filter? I allso have some other filters that I would like to assign to a button. And allso a button that turns off all the filters.

Can anyone guide me in that?

 

[Luke M]

Sure thing. The easiest thing would be to record a macro of you setting a filter on that specific column, so you get the correct range and Field reference. Then, the back half of the line should look similar to this one:

[pre]

Sub ExampleCode()

'
ActiveSheet.Range("$A$2:$B$20").AutoFilter Field:=1, Criteria1:= _
">=" & Date, Operator:=xlAnd, Criteria2:="<" & Date + 14
End Sub

[/pre]
You can see in the code that the Criteria will be set using today's date.