Autofilter problem

L

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:D (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
 
C

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:D (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.
Click to expand...

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
 
L

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
Click to expand...

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
 
L

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")
Click to expand...

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
 
L

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
Click to expand...

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
 
C

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 :)
Click to expand...

look in the VBA help for:
XlDynamicFilterCriteria-Enumeration


Regards
Claus Busch
 
L

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
Click to expand...

Hi Claus,

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

Regards,
Ludo
 
C

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.
Click to expand...

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Problem with date formatting 5
AutoFilter Criteria using named range 4
Autofil on variable rows, delete extract and show remaining rows 3
Xl2007 Autofilter - ValueList problem 7
AutoFilter Issue 2
Filter based on Date 8
Record Click on Error Indicator 2
Copy Autofilter Source Workbook A result in Destination Workbook BSheet1 1

Top