Autofilter problem

Hi,
Excel 2007/2010
I'm trying to use the autofilter on a range using the actual date as a criteria.
I have following code but it fails on the indicated code line.

once i can autofilter for the actual date, then i need to select the apropriate data from columns B (visible rows) to send them to a recipient with mail.
For sending mail, i'll visit the site of Ron De Bruin.

I expect that RngToFilter is a valid range, so hoping i can address this imediately in the code as follow:
RngToFilter.AutoFilter Field:=1, Operator:=xlFilterValues, Criteria2:=Array(2, strToday)'<<<<<< error 1004 Method Autofilter from Class Range fails
but it fails.
Any idea what i'm doing wrong?

Sub FilterByToday()
'
Dim w As Worksheet
Dim RngToFilter As Range
Dim strToday As String
'get actual date
strToday = Date

Set w = Worksheets("Sheet1")
With w
'reset the autofilter mode
.AutoFilterMode = False
'set range to filter to the current region without the headers
Set RngToFilter = .Cells.CurrentRegion
RngToFilter.Offset(1, 0).Resize(RngToFilter.Rows.Count - 1, RngToFilter.Columns.Count).Select
Set RngToFilter = Selection
'use this range to filter on the actual date
RngToFilter.AutoFilter Field:=1, Operator:=xlFilterValues, Criteria2:=Array(2, strToday)'<<<<<< error 1004 Method Autofilter from Class Range fails

End With
'reset the autofilter mode
w.AutoFilterMode = False
'clean up
Set RngToFilter = Nothing
Set w = Nothing
End Sub

here's my test data:

Date article # serialnumber Description
27/07/2012 1 a aaaaaa
27/07/2012 2 b aaaaaa
27/07/2012 3 c ssssss
27/07/2012 4 d ddddd
27/07/2012 5 e cdccccc
27/07/2012 6 f xvbvfb
28/07/2012 1 a qdffg
28/07/2012 2 b svght
28/07/2012 3 c scdbf
28/07/2012 4 d ggjku
28/07/2012 5 e fgrrjk
28/07/2012 6 f xcvbfbn
28/07/2012 7 g adgvfhgj

Any help welcome,

Regards,
Ludo

Hi Ludo,

Am Sat, 28 Jul 2012 14:17:07 -0700 (PDT) schrieb Ludo:

> I'm trying to use the autofilter on a range using the actual date as a criteria.
> I have following code but it fails on the indicated code line.
>
> once i can autofilter for the actual date, then i need to select the apropriate data from columns B (visible rows) to send them to a recipient with mail.
> For sending mail, i'll visit the site of Ron De Bruin.
>
> I expect that RngToFilter is a valid range, so hoping i can address this imediately in the code as follow:
> RngToFilter.AutoFilter Field:=1, Operator:=xlFilterValues, Criteria2:=Array(2, strToday)'<<<<<< error 1004 Method Autofilter from Class Range fails
> but it fails.

Try:

Sub FilterByToday()

Dim wsh As Worksheet
Dim RngToFilter As Range

Set wsh = Worksheets("Sheet1")
With wsh
.AutoFilterMode = False
Set RngToFilter = .Cells.CurrentRegion
RngToFilter.AutoFilter Field:=1, Criteria1:=xlFilterToday, _
Operator:=xlFilterDynamic
End With

Set RngToFilter = Nothing
Set wsh = Nothing
End Sub

Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

On Sat, 28 Jul 2012 14:17:07 -0700 (PDT), Ludo > wrote:

>Hi,
>Excel 2007/2010
>I'm trying to use the autofilter on a range using the actual date as a criteria.
>I have following code but it fails on the indicated code line.
>
>once i can autofilter for the actual date, then i need to select the apropriate data from columns B (visible rows) to send them to a recipient with mail.
>For sending mail, i'll visit the site of Ron De Bruin.
>
>I expect that RngToFilter is a valid range, so hoping i can address this imediately in the code as follow:
> RngToFilter.AutoFilter Field:=1, Operator:=xlFilterValues, Criteria2:=Array(2, strToday)'<<<<<< error 1004 Method Autofilter from Class Range fails
>but it fails.
>Any idea what i'm doing wrong?
>
>Sub FilterByToday()
>'
> Dim w As Worksheet
> Dim RngToFilter As Range
> Dim strToday As String
>'get actual date
> strToday = Date
>

I'm pretty sure that the date needs to be in the format of m/d/yyyy. VBA is US-Centric
So try strToday = format(date, "m/d/yyyy")

Op zaterdag 28 juli 2012 23:37:25 UTC+2 schreef Claus Busch het volgende:
> Hi Ludo,
>
>
>
> Am Sat, 28 Jul 2012 14:17:07 -0700 (PDT) schrieb Ludo:
>
>
>
> > I'm trying to use the autofilter on a range using the actual date as a criteria.
>
> > I have following code but it fails on the indicated code line.
>
> >
>
> > once i can autofilter for the actual date, then i need to select the apropriate data from columns B (visible rows) to send them to a recipient with mail.
>
> > For sending mail, i'll visit the site of Ron De Bruin.
>
> >
>
> > I expect that RngToFilter is a valid range, so hoping i can address this imediately in the code as follow:
>
> > RngToFilter.AutoFilter Field:=1, Operator:=xlFilterValues, Criteria2:=Array(2, strToday)'<<<<<< error 1004 Method Autofilter from Class Range fails
>
> > but it fails.
>
>
>
> Try:
>
>
>
> Sub FilterByToday()
>
>
>
> Dim wsh As Worksheet
>
> Dim RngToFilter As Range
>
>
>
> Set wsh = Worksheets("Sheet1")
>
> With wsh
>
> .AutoFilterMode = False
>
> Set RngToFilter = .Cells.CurrentRegion
>
> RngToFilter.AutoFilter Field:=1, Criteria1:=xlFilterToday, _
>
> Operator:=xlFilterDynamic
>
> End With
>
>
>
> Set RngToFilter = Nothing
>
> Set wsh = Nothing
>
> End Sub
>
>
>
> Regards
>
> Claus Busch
>
> --
>
> Win XP PRof SP2 / Vista Ultimate SP2
>
> Office 2003 SP2 /2007 Ultimate SP2

Hi Claus,

Thanks for the info.
It works fine.

Additional question:
How did you know that you can use Criteria1:=xlFilterToday?
I can't find this back in the Help for Excel.
I guess that this knowledge belongs to the real experts .

Where can i find more 'expert' info abouth this topic (Autofilter)?

Regards,
Ludo

<SNIP>
>
>
>
> I'm pretty sure that the date needs to be in the format of m/d/yyyy. VBA is US-Centric
>
> So try strToday = format(date, "m/d/yyyy")

Hi Ron,

Tried it in my original code dut get still the same error.
The sollution from Claus works pritty well.

Anyhow thanks a lot for your remark.
Working with date and time is sometimes a tricky issue.

Regards,
Ludo

<SNIP>
>
> Try:
>
>
>
> Sub FilterByToday()
>
>
>
> Dim wsh As Worksheet
>
> Dim RngToFilter As Range
>
>
>
> Set wsh = Worksheets("Sheet1")
>
> With wsh
>
> .AutoFilterMode = False
>
> Set RngToFilter = .Cells.CurrentRegion
>
> RngToFilter.AutoFilter Field:=1, Criteria1:=xlFilterToday, _
>
> Operator:=xlFilterDynamic
>
> End With
>
>
>
> Set RngToFilter = Nothing
>
> Set wsh = Nothing
>
> End Sub
>
>
>
> Regards
>
> Claus Busch
>
> --
>
> Win XP PRof SP2 / Vista Ultimate SP2
>
> Office 2003 SP2 /2007 Ultimate SP2

Hi Claus,

It works fine.
Thanks for your help.

Additional question:
How did you know that you can use Criteria1:=xlFilterToday?
Can't find this back in the Help for Excel
I guess that this is 'expert' knowledge

Where can i find more of this 'expert' info?
Online, in books, ...

Regards,
Ludo

Hi Ludo,

Am Mon, 30 Jul 2012 05:08:50 -0700 (PDT) schrieb Ludo:

> Additional question:
> How did you know that you can use Criteria1:=xlFilterToday?
> Can't find this back in the Help for Excel
> I guess that this is 'expert' knowledge

look in the VBA help for:
XlDynamicFilterCriteria-Enumeration


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Op maandag 30 juli 2012 14:19:16 UTC+2 schreef Claus Busch het volgende:
> Hi Ludo,
>
>
>
> Am Mon, 30 Jul 2012 05:08:50 -0700 (PDT) schrieb Ludo:
>
>
>
> > Additional question:
>
> > How did you know that you can use Criteria1:=xlFilterToday?
>
> > Can't find this back in the Help for Excel
>
> > I guess that this is 'expert' knowledge
>
>
>
> look in the VBA help for:
>
> XlDynamicFilterCriteria-Enumeration
>
>
>
>
>
> Regards
>
> Claus Busch
>
> --
>
> Win XP PRof SP2 / Vista Ultimate SP2
>
> Office 2003 SP2 /2007 Ultimate SP2

Hi Claus,

Thanks for the verry fast reply.
I will check immediately.

Regards,
Ludo

Hi Ludo,

Am Mon, 30 Jul 2012 05:23:54 -0700 (PDT) schrieb Ludo:

> Thanks for the verry fast reply.
> I will check immediately.

like Ron posted, your date must have the format MM/DD/YYYY.
But I think you don't have a english version of excel.
Formatting the date is not usefull therefore you have to create the
string:
strToday = Month(Date) & "/" & Day(Date) & "/" & Year(Date)
and use it:
RngToFilter.AutoFilter Field:=1, Operator:=xlFilterValues, Criteria1:=strToday

Or you write =Today() in G1 and then use it like this:
..AutoFilter Field:=1, Criteria1:=">=" & .Range("G1").Value2



Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

On Mon, 30 Jul 2012 05:00:42 -0700 (PDT), Ludo > wrote:

><SNIP>
>>
>>
>>
>> I'm pretty sure that the date needs to be in the format of m/d/yyyy. VBA is US-Centric
>>
>> So try strToday = format(date, "m/d/yyyy")
>
>Hi Ron,
>
>Tried it in my original code dut get still the same error.
>The sollution from Claus works pritty well.
>
>Anyhow thanks a lot for your remark.
>Working with date and time is sometimes a tricky issue.
>
>Regards,
>Ludo

It is tricky. If you want to explore further, I'd try formatting strDate so that it matches the Short Date setting in your Windows Regional settings dialog (NOT the Excel Format). I suppose that might be d/m/yyyy or "short date"