Autofilter, Error 13, Type Mismatch

[jynxy]

Hi,

Im getting error 13 type mismatch when trying to filter dates not equal to. if i remove "<>" & it works fine. how can i filter all dates not equal to the date and delete.

With .UsedRange.Columns("A:G")
                .AutoFilter Field:=5, Operator:=xlFilterValues, _
                        Criteria2:="<>" & Array(2, Format(dt2, "mm/dd/yyyy"))
                Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
                '.AutoFilter
            End With

Thanks

 

[RoryA]

For a single value it would just be:

With .UsedRange.Columns("A:G")
                .AutoFilter Field:=5, Criteria1:="<>" & Format(dt2, "mm/dd/yyyy")
                Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
                '.AutoFilter
            End With

 

[jynxy]

@RoryA if i remove the Array it stops working, i assume this is because it is a date time column ?

 

[RoryA]

Yes, if there are time portions, that won't work. You'd need to use two criteria with an Or operator - one for is before the date, and one for is on or after the day after the date. So something like:

.AutoFilter Field:=5, Criteria1:= _
        "<" & Format(dt2, "mm/dd/yyyy"), Operator:=xlOr, Criteria2:=">=" & Format(dt2 + 1, "mm/dd/yyyy")

 

[jynxy]

Thank you that worked, i had that earlier but didnt consider the +1