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]

1. CopyTo range, DestinationRange, must be the activesheet. So, there is no need for a DinD for it.

2. If formulas in the criterion range, set them as hardcoded values.

 

[Fluff]

That code works happily for me. Are you sure the change event is being triggered?
Also which sheet is the code in?

 

[Kevin0427]

OK so I changed it to this and still not filtering. Please explain further what #2 means...

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 = Range("C4:C6")
Set DestinationRange = 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

 

[Kevin0427]

That code works happily for me. Are you sure the change event is being triggered?
Also which sheet is the code in?

code is in DinD Sheet. All code runs and is triggered. just does not filter.

 

[Fluff]

Does C4 match the one of the headers in the data range?
Also you are triggering the filter if C5 is changed, what is in C6?

 

[Kevin0427]

Does C4 match the one of the headers in the data range?
Also you are triggering the filter if C5 is changed, what is in C6?

Copied and Pasted header. I can double check to b sure. Nothing in C6 yet. Are both required to filter? I thought the second line was an or function

 

[Kevin0427]

Hummmmm…… when I put a value in C6 it worked. I am filtering by a date range. How can I do it if they only fill in a start date?

I want either anything before or after a date or;
anything between two dates

 

[Fluff]

What exactly you putting in C5 & 6?
If I leave one of them empty I get the entire data set.

 

[Kevin0427]

What exactly you putting in C5 & 6?
If I leave one of them empty I get the entire data set.

That is exactly my problem. C5 is a start date (>=8/15/2019) and C6 is the end date. What if they want everything after the start date or everything before the end date? ie everything since 8/1/2019