CODEWITHSUNDEEP
vbaexcel
Smudger
Smudger

Reputation: 10771

Filter Not Accepting Date Variable

trying my hand at some vba that I have copied and cahnged.

This works well

With ActiveSheet
        .AutoFilterMode = False
        rng1.AutoFilter Field:=1, Criteria1:="<>2014-12-02"
        rng1.Offset(1, 0).EntireRow.Delete
        .AutoFilterMode = False
    End With

I have created a variable called factorydate

factorydate = Worksheets("systemdata").Cells(3, "C").Value

How do I replace the date '2014-12-02' with the variable factorydate? something like:

With ActiveSheet
        .AutoFilterMode = False
        rng1.AutoFilter Field:=1, Criteria1:="<>" & factorydate
        rng1.Offset(1, 0).EntireRow.Delete
        .AutoFilterMode = False
    End With

Upvotes: 1

Views: 68

Answers (1)

Chrismas007
Chrismas007

Reputation: 6105

If the value in .Cells(3, "C").Value is a date (or you Dim factorydate as Date) then the order will change of the numbers because of date default ordering. IF IT IS A DATE VALUE, I would try:

rng1.AutoFilter Field:=1, Criteria1:="<>" & year(factorydate) & "-" & month(factorydate) & "-" & day(factorydate)

to make sure it puts your syntax in correctly

EDIT: or do this:

factorydate = Format(factorydate, "yyyy-MM-dd")
rng1.AutoFilter Field:=1, Criteria1:="<>" & factorydate

2EDIT: final option:

factorydate = Worksheets("systemdata").Cells(3, "C").Text

using .Text returns the exact text string to ensure your filter applies correctly.

Upvotes: 1

Related Questions