Date filter for all periods

[mfputer]

Does anyone know how to create an “AllDateinperio d” filter with dynamic dates. Autofilter only allows filtering by month and quarters, I want specific date range. I am new at VBA and need some help.

I have 5 years of data (41K rows) and want to filter for specific date ranges and show all years.

Currently I created a filter using VBA and a userform, however it only returns data for the specific date range year. I want to expand it to bring back all data no matter the year.

 Sheets("Data").Visible = True
Sheets("Data").Select
        Selection.AutoFilter
    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:= _
        ">=" & tbStDate, Operator:=xlAnd, Criteria2:="<=" & tbEndDate

Sheets("Data").Visible = False
UserForm2.Hide

[twinnyfo]

I am a little confused by your post, but if I interpret your post correctly, you are looking for records that have date ranges within certain days/months, regardless of the year? Correct?

I would point you in the direction of using the Month() and Day() functions which are built into VBA. You could check to see which Month/Day the date in the Table falls (this would be irrespective of the year), and if the record meets your criteria, then it will be displayed.

It also looks like this is in Excel? This is the MS Access Forum, but much of what you can do with Access VBA can also be done with Excel--although I am not an expert at Excel VBA manipulation.

[mfputer]

Thank you for the reply. Yes you are correct this is an Excel question. I interpreted the forum as also VBA question not only Access.

[Rabbit]

This thread has been moved to the Excel forum.

[Luuk]

twinnyfo asked:
I am a little confused by your post, but if I interpret your post correctly, you are looking for records that have date ranges within certain days/months, regardless of the year? Correct?

@mfputer: Can you comment on that?

[zmbd]

XL2010 Custom Date Range Auto Filter

For Excel2010, if I understand you correctly, this is actually built into the program - no need to code:

Setup your autofilters and then select custom filter as shown in the following image.

(I've done a few screen-shots and a cut-n-past to get this image).
[imgnothumb]http://bytes.com/attachment.php? attachmentid=77 93[/imgnothumb]
As you can see you can use wild cards here.
Also... I had alot more preset filters than what you've stated... so I wonder which version you are using.

Then there is also this... please note, once again a cut and paste of screen shots.... I selected month from the drop down list first and then entered the name of the month
[imgnothumb]http://bytes.com/attachment.php? attachmentid=77 94[/imgnothumb]
Notice the "add current selection to filter..."

So there are a few options...