Autofilter problem

[Ludo]

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

 

[Claus Busch]

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

 

[Ron Rosenfeld]

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")

 

[Ludo]

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:










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

 

[Ludo]

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

 

[Ludo]

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

 

[Claus Busch]

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

 

[Ludo]

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:









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

 

[Claus Busch]

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

 

[Ron Rosenfeld]

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"