I would like to hide all dates from today and backward, only data with
tomorrow and future dates will be shown on the list, how do I set the date in
criterial please?
Selection.AutoFilter Field:=5, Criteria1:="<Today()"
Regards

Depends on your regional date format. Generally autofilter does not like
d/m/yyyy dates in the code even though it appears that way when recorded.
Needs to be m/d/yyyy format even if the dates are formatted as d/m/yyyy in
the worksheet.

ActiveSheet.AutoFilter.Range. _
AutoFilter Field:=5, _
Criteria1:="<" & Format(Date, "m/d/yyyy")

However, if your regional area dates are d/m/y then using the alpha month
appears to work as follows

ActiveSheet.AutoFilter.Range. _
AutoFilter Field:=5, _
Criteria1:="<" & Format(Date, "d mmm yyyy")

Also better to use AutoFilter.Range than selection.

--
Regards,

OssieMac


"Seeker" wrote:

I would like to hide all dates from today and backward, only data with
tomorrow and future dates will be shown on the list, how do I set the date in
criterial please?
Selection.AutoFilter Field:=5, Criteria1:="<Today()"
Regards

You'd want to use something like:

Selection.AutoFilter Field:=5, Criteria1:="<" & date

Saved from a previous post:

This is from "Excel 2002 VBA Programmer's Reference"
Written by John Green, Stephen Bullen, Rob Bovey and Robert Rosenberg

http://www.oaltd.co.uk:80/ExcelProgR...rogRefCh22.htm
Search for "Range.AutoFilter" and you'll see this note:

Range.AutoFilter

The AutoFilter method of a Range object is a very curious beast. We are forced
to pass it strings for its filter criteria and hence must be aware of its string
handling behaviour. The criteria string consists of an operator (=, , <, =
etc.) followed by a value.

If no operator is specified, the "=" operator is assumed. The key issue is that
when using the "=" operator, AutoFilter performs a textual match, while using
any other operator results in a match by value. This gives us problems when
trying to locate exact matches for dates and numbers.

If we use "=", Excel matches on the text that is displayed in the cell, i.e. the
formatted number. As the text displayed in a cell will change with different
regional settings and Windows language version, it is impossible for us to
create a criteria string that will locate an exact match in all locales.

There is a workaround for this problem. When using any of the other filter
criteria, Excel plays by the rules and interprets the criteria string according
to US formats. Hence, a search criterion of "=02/01/2001" will find all dates
on or after 1st Feb, 2001, in all locales.

We can use this to match an exact date by using two AutoFilter criteria. The
following code will give an exact match on 1st Feb, 2001 and will work in any
locale:

Range("A1:D200").AutoFilter 2, "=02/01/2001", xlAnd, "<=02/01/2001"

==========

So you may want something like this for July of 2009:

YourRange.AutoFilter Field:=5, _
Criteria1:="<" & format(Date, "mm/dd/yyyy")

Seeker wrote:

I would like to hide all dates from today and backward, only data with
tomorrow and future dates will be shown on the list, how do I set the date in
criterial please?
Selection.AutoFilter Field:=5, Criteria1:="<Today()"
Regards

--

Dave Peterson

one way around the problem ossiemac has highlighted when using AutoFilter
with dates is to pass the date to a Long Variable using the DateSerial
function. All dates in Excel are stored as Long integers so in theory, should
be able to make use of this if you format your dates non US. However a note
of caution, while this may work for dates greater or less than a date being
tested, it may fail on filtering for an exact date.

Following code example not tested & will need correcting for your need but
hopefully, of some help to you.

Sub FilterDate()
Dim TodayDate As Date
Dim LongDate As Long
Dim ws As Worksheet


Set ws = Worksheets("Sheet1")


TodayDate = DateSerial(Year(Date), _
Month(Date), _
Day(Date))

LongDate = TodayDate

With ws

.Range("A1").AutoFilter

.Range("A1").AutoFilter Field:=1, _
Criteria1:="<" & LongDate, _
VisibleDropDown:=False

End With


End Sub

--
jb


"Seeker" wrote:

I would like to hide all dates from today and backward, only data with
tomorrow and future dates will be shown on the list, how do I set the date in
criterial please?
Selection.AutoFilter Field:=5, Criteria1:="<Today()"
Regards