Advanced Filter not filtering

[Kevin0427]

It is probably a dumb mistake but I am not seeing it. All this code runs all the way through but does not filter the results.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim DataRange As Range
Dim FilterRange As Range
Dim DestinationRange As Range
Set DataRange = DataSheet.Range("A1").CurrentRegion
Set FilterRange = DinD.Range("C4:C6")
Set DestinationRange = DinD.Range("C37").CurrentRegion
If Target.Address = "$C$5" Then
    
    Application.EnableEvents = False
    DestinationRange.Offset(1).ClearContents
    DataRange.AdvancedFilter xlFilterCopy, FilterRange, DestinationRange
    Application.EnableEvents = True
    
End If
End Sub

 

[Kenneth Hobson]

Can you post a link to a shared site with a simple file?

Dates are a bit tricky. You will likely need ">=dateasnumber" sort of thing.

As coded, they would need to change C6 and then C5 to get the range criterion. I would probably use other cells that they update and if either of those change, it would fire the event with the criterion fields set by the Change event.

 

[Kevin0427]

Can you post a link to a shared site with a simple file?

Dates are a bit tricky. You will likely need ">dateasnumber" sort of thing.

As coded, they would need to change C6 and then C5 to get the range criterion. I would probably use other cells that they update and if either of those were change, it would fire the event with the criterion fields set by the Change event.

I realize that. I was jut trying to get it to work before adding another layer. I have since changed the IF to include C6 too.

 

[Fluff]

To filter between values they must be on the line, so you need the header in C4 & D4 & then the criteria in C5 & D5

 

[Kevin0427]

To filter between values they must be on the line, so you need the header in C4 & D4 & then the criteria in C5 & D5

OK will that work if they only fill in one date?

 

[Kevin0427]

To filter between values they must be on the line, so you need the header in C4 & D4 & then the criteria in C5 & D5

Can I merge C4 & D4?

 

[Kevin0427]

To filter between values they must be on the line, so you need the header in C4 & D4 & then the criteria in C5 & D5

oops. hold on wait. The between is working on two lines. What is not working is when only one date is entered. ie >=8/1/2019 but no end date is entered.

Do I just need to hardcode in a date if one is blank? Not a very pretty solution.

 

[Fluff]

Putting them in C5 & D5 works for me if D5 is blank.

Can I merge C4 & D4?

NO, do not merge cells.

 

[Kevin0427]

Putting them in C5 & D5 works for me if D5 is blank.
NO, do not merge cells.

So same header in both columns. Start and End Date on same row. Filters all of these choices:

1. > 8/1/019; and
2. <8/1/2019; and
3.>8/1/2019 and <8/30/2019

 

[Fluff]

Are you saying it's now working?

 

[Kevin0427]

Are you saying it's now working?

Sorry. I was out the rest of the day. No it is not working. I tested your suggestion today. If I put in a start date and no end date or an end date with no start date it does not work. Still looking for a solution. In the meantime I have hardcoded it to add the missing date if it is blank.