CODEWITHSUNDEEP
excelvbadate
Carlsberg789
Carlsberg789

Reputation: 155

VBA Date filter

I have a problem with a date filter. VBA doesn't work on it for some reason, if i do the exact same procedure manually, the filter works fine but with the VBA the filter returns "nothing", which is false.

So basically i have date values in column 8 and i want to see the ones that are after my ComparisonDate.

Any ideas on why this is not working? 

Sub Datefilter()

    If ActiveSheet.AutoFilterMode = True Then
    ActiveSheet.AutoFilterMode = False
    End If

headerRow = Range("Headers").Row
lRow = Range("E1048576").End(xlUp).Row
ComparisonDate = Sheets("Start").Range("Date_CP").Value

Range("A" & headerRow & ":M" & lRow).AutoFilter field:=8, Criteria1:=">" & ComparisonDate

End Sub

Upvotes: 0

Views: 140

Answers (1)

Storax
Storax

Reputation: 12167

I assume ComparisonDate is indeed a date then you need to add to your code the following line and convert the date to a double value.

ComparisonDate = CDbl(ComparisonDate)

that means your code should look like that

Sub Datefilter()

    If ActiveSheet.AutoFilterMode = True Then
        ActiveSheet.AutoFilterMode = False
    End If

    headerRow = Range("Headers").Row
    lRow = Range("E1048576").End(xlUp).Row
    ComparisonDate = Sheets("Start").Range("Date_CP").Value
    ComparisonDate = CDbl(ComparisonDate)

    Range("A" & headerRow & ":M" & lRow).AutoFilter field:=8, Criteria1:=">" & ComparisonDate

End Sub

Upvotes: 2

Related Questions