excel vba autofilter not filtering on dates

Question

I have column of dates, which is an output from a database. There is a macro that filters on this date which is meant to show all dates older than 14 days from current date excluding nulls. The filter when applied filters the entire sheet and shows nothing. When I take off the - 14 I do get dates, I have tried even using alt a, e, f to ensure that the column is the correct format, but no luck.

Here is my code snippet:

ActiveSheet.UsedRange.AutoFilter Field:=34, Criteria:="<>NULL", _
Operator:=xlAnd, Criteria2:="<" & Now()-14

I have tried changing the criteria2 to "<" & Date - 14 & "# 00:00:00 AM#"

Please help

Siddharth Rout · Accepted Answer · 2012-05-21 16:40:22Z

1

Try this

Change Criteria:="<>NULL" to Criteria1:="<>NULL"

FOLLOWUP

The code was not working as the headings were in not in Row 1. Once the exact range was specified, it started working.

Option Explicit

Sub Sample()
    Dim lRow As Long

    lRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

    ActiveSheet.AutoFilterMode = False

    ActiveSheet.Range("$A$2:$BV$" & lRow).AutoFilter Field:=34, Criteria1:="<>NULL" _
    , Operator:=xlAnd, Criteria2:="<" & Format(Date - 14, "0")
End Sub

edited May 21, 2012 at 16:40

answered May 21, 2012 at 15:17

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Andy5 Over a year ago

Sorry, that's a typo error on my part as I was typing it was another screen, but that is not the error

Siddharth Rout Over a year ago

In that case check the format of the date values in the Excel sheet. Hope they are not stored as text? I have tested your above code and it works.

Siddharth Rout Over a year ago

If it still doesn't help then can I see a sample as well if possible

Andy5 Over a year ago

What is the best to check this, because all I know it is from an Oracle database in the following format = dd/mm/yyyy HH:MM and what is the best way to solve this

Andy5 Over a year ago

How do you want me to give the sample date? All the date is 06/04/2011 07:17

|

Collectives™ on Stack Overflow

excel vba autofilter not filtering on dates

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related