VBA - Filter between two dates

[Mr Stanford]

Can anyone help on the following query?

I have taken data from a gantt chart in microsoft project and am looking to filter between dates by dropdown boxes and a macro button, this is to show all of the activities that are happening between the selected dates.

I have the start date in column 6 and the finish date in column 7.

I would like the user to select a start date and an end date and any task name item that occurs between those dates are shown.

Currently I have the code as shown below, but I know it is wrong as it filters the start date, then filters the start date results and add the finish date filter to the existing filter.

Sub Between()
Sheets("Sheet1").Select
If Sheets("Sheet1").AutoFilterMode Then Cells.AutoFilter


    
    
 Dim lDateFrom As Long
    Dim lDateTo As Long
     
    If Range("Sheet6!L14") > Range("Sheet6!L16") Then
        lDateTo = CDbl(Range("Sheet6!L14"))
        lDateFrom = CDbl(Range("Sheet6!L16"))
    Else
        lDateTo = CDate(Range("Sheet6!L16"))
        lDateFrom = CDate(Range("Sheet6!L14"))
    End If


     
    With Sheets("Sheet1")
        Range("$A$4:$Q$220").AutoFilter Field:=6, Criteria1:=">=" & lDateFrom, Operator:=xlAnd, Criteria2:="<=" & lDateTo, _
        Operator:=xlAnd
        Range("$A$4:$Q$220").AutoFilter Field:=7, Criteria1:=">=" & lDateFrom, Operator:=xlAnd, Criteria2:="<=" & lDateTo
    End With
    
End Sub

 

[steve the fish]

Not sure you could do that with an autofilter unless you employed a slave column. Whether each line is displayed is not dependant on one cell but two.

 

[steve the fish]

Now thinking about it im trying to think that if start date (data) is less than end date (filter criteria) and if start date (filter criteria) is less than end date (data) then they overlap....

 

[Mr Stanford]

I have a filter on project that is set up as follows;

Finish is greater than or equal to "start or finish after start date"
and Start is less "end date".

But not sure how to do that with filters in excel.

 

[steve the fish]

Try filtering the start date column with the criteria less than end of February. Then filter the end date column with the criteria greater than the 1st of January.

 

[hicksi]

It's doable. But beware:- filtering under date can need some special formatting of the date (particularly if you aren't Ummerican)

 

[hicksi]

Suppose the Filter dates are 1-April-2016 through 30-Jun-2016

What tasks are you wanting to see?
Anything that:
- starts before 30-Jun AND (doesn't end OR ends after 1-April) {call these tests A, B, C)

2-Jan to 13-Mar fails C
2-Jan to 13-Apr Pass
2-Jan to 13-Jul Pass
2-Jan to Unfunished Pass

2-Apr to 13-Apr Pass
2-Apr to 13-Jul Pass
2-Apr to Unfinished Pass

2-Jul to 13-Jul fails A
2-Jul to Unfinished fails A

 

[hicksi]

OOPS. I think that's what Mr Stanford said. Sorry for stepping on your toes.

 

[hicksi]

With Sheets("Sheet1") 
     Range("$A$4:$Q$220").AutoFilter Field:=6, Criteria1:="<=" & lDateTo
     Range("$A$4:$Q$220").AutoFilter Field:=7, Criteria1:=">=" & lDateFrom, Operator:=xlOr, Criteria2:="="
End With

 

[Mr Stanford]

Hicksi - Simple as that! Works flawlessly. Thank you.

Steve the fish - thank you also.