CODEWITHSUNDEEP
vbafilter
lakshmen
lakshmen

Reputation: 29064

Filter off dates lesser than currentDate in VBA

I am trying to filter off dates in the column which have a date lesser than today. I have written the code but it doesn't seem to work.

Sub FilterByDate()

Dim ws1 As Worksheet
Set ws1 = ActiveWorkbook.Worksheets("Sheet1")
Set rng1 = ws1.Range(ws1.[c2], ws1.Cells(Rows.count, "C").End(xlUp))
Dim todayDate As Date
todayDate = Format(Date, "dd/mm/yyyy")
rng1.AutoFilter Field:=1, Criteria1:="<" & todayDate
rng1.Offset(1, 0).EntireRow.Delete
ws1.AutoFilterMode = False

End Sub

This code doesn't filter for the dates lesser than today. When I filter, the list doesn't seem to change. Need some help on this.

EDIT:

15/03/2016
15/12/2015
17/11/2015
14/01/2016
18/02/2016
14/04/2016
15/12/2015
17/11/2015
14/01/2016
17/11/2015

enter image description here

Upvotes: 1

Views: 61

Answers (1)

David Zemens
David Zemens

Reputation: 53623

At the end of your AutoFilter statement, do:

Operator:=xlAnd

So that line shall become:

rng1.AutoFilter Field:=1, Criteria1:="<" & todayDate, Operator:=xlAnd

Must be something different about date filters where this operator is required. I will not claim to having known this in advance, I used the macro recorder to figure it out :)

enter image description here

Additional infos from the comments:

Your problem may be related to system locale, and VBA's "feature" of using US English dates, internally.

  • First: the dates you provide as examples are not valid US-English dates, and will not resolve as such (one particular fun problem with dates is that 3/6/2015 resolves as June 3 to the rest of the world, while it is March 6 in US English. Values like 17/11/2015 are not dates at all.

  • Additionally: there may be some bad juju going on in this statement:

    todayDate = Format(Date, "dd/mm/yyyy")

    Here, you are taking a date (long integer, which results from Date() function) and casting it to string via Format() which returns a string, then you're putting that string in to the todayDate variable. Even if you have resolved the issue with locale settings, this statement may be problematic in its own right.

Upvotes: 1

Related Questions