Autofilter by date range

[joeb_444]

Hi All,

I'm completely new to VBA so please bear with me. I've been reading various sources for the past few hours on how to autofilter a table based on 2 variable dates. I've tried various different scripts but can't get it to work. Please could someone show me what I'm doing wrong:

Background:
I have a simple table of data with column 1 being a date input - the column headers are at B8:G8.
At cell K5 I have a start date which is user entered
At cell J5 I have an end date which is user entered
I wish to filter the table for entries between these 2 dates.

At the minute my code reads as:

Sub FilterJB()

Dim DateStart As Date
Dim DateEnd As Date

DateStart = [K5].Value
DateEnd = [L5].Value

' Set the filter

Range("B8:G8").Select
Selection.AutoFilter

ActiveSheet.Range("$B$8:$G$5005").AutoFilter Field:=2, Criteria1:="J5", Operator:=xlAnd
ActiveSheet.Range("$B$8:$G$5005").AutoFilter Field:=1, Criteria1:=">=" & DateStart, Operator:=xlAnd, Criteria2:="<=" & DateEnd


End Sub

Any help would be much appreciated.

Best wishes

Joe

[JP Romano]

It looks like you may have your cells mixed up (unless I'm reading something wrong).
Start date = K5
End date = L5

I'd also use the variables you're setting:

Please Login or Register  to view this content.

Note that this only applies that filter to the first column. You could fairly easily amend it to apply to each individual column by changing the value after Activesheet.range...

Good luck!

[joeb_444]

Hi thanks for the prompt response:

I've tried the code you provided but I get 'runtime error 13, type mismatch' and the debug window highlights DateEnd = [L5].Value?

[joeb_444]

Ok I've been playing around a little more and currently have this code:

Please Login or Register  to view this content.

I forgot to mention that I'm also wanting to filter by a particular member field as well as the date range which is the first filter criteria.

I'm confused as when I run the script, no entries are filtered but when I manually view the autofilter settings using the drop downs on each column, they are all set to the correct values so it's sort of working. As soon as I hit OK on any one of the filter settings, the results appear? It's almost as though it needs refreshing after the values are set?

[JP Romano]

Hi... can you attach your file so I can work with the proper info? The code I sent you worked fine for me, but if I can see exactly what you have I may be able to help a bit better.
Thanks Joe!

[joeb_444]

Hi... can you attach your file so I can work with the proper info? The code I sent you worked fine for me, but if I can see exactly what you have I may be able to help a bit better.
Thanks Joe!

Thanks for the offer to look at my spreadsheet which I've attached.

To explain a little better, the spreadsheet is basically to record fuel usage by members of a fuel syndicate. The first tab is where each transaction will be recorded in a table and also where I'd like the autofilter to operate. I've set up a filter criteria area top right which is where the member will be selected along with a date range. I've managed to get the 'turn filter off' button to work!

Thanks very much for your help.

[JP Romano]

Okay, I see the issue.

The dates are formatted differently. I modifed all the dates to use the same format:
mm/dd/yy
Then added a line to select the filter for the dates seperately and it worked like a charm.

Please Login or Register  to view this content.

Try putting your dates all in the same format, whatever it may be, then let me know how it goes.

Good luck!

[broro183]

Here's a link which explains how autofilter interprets date values: http://www.ozgrid.com/VBA/autofilter-vba-dates.htm
I haven't worked through an example myself but it may explain why it works for JP (USA) but not Joeb in the UK.

Rob