| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter Criteria not filtering when using NOW()
Hi,
I have this line of code. Selection.AutoFilter Field:=Range("Note1").Column, Criteria1:="<=" & Format(Now(), "dd/mm/yyyy") I want to filter anything dated today or prior. It's not working - every line is being filtered out. When I drop down the autofilter button for the column, and go to 'Custom' I can see the correct date is in the dialog box, and the filter works when I press OK through it. Anyone know why this isn't working? Cheers Tony |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofilter Criteria not filtering when using NOW()
try doing
Selection.AutoFilter Field:=Range("Note1").Column, _ Criteria1:="<=" & Now() -- Regards, Tom Ogilvy "bony_tony" wrote: Hi, I have this line of code. Selection.AutoFilter Field:=Range("Note1").Column, Criteria1:="<=" & Format(Now(), "dd/mm/yyyy") I want to filter anything dated today or prior. It's not working - every line is being filtered out. When I drop down the autofilter button for the column, and go to 'Custom' I can see the correct date is in the dialog box, and the filter works when I press OK through it. Anyone know why this isn't working? Cheers Tony |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofilter Criteria not filtering when using NOW()
Try DateSerial Selection.AutoFilter Field:=Range("Note1").Column, Criteria1:="<=" & DateSerial(Year(Date), Month(Date), Day(Date)) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "bony_tony" wrote in message ups.com... Hi, I have this line of code. Selection.AutoFilter Field:=Range("Note1").Column, Criteria1:="<=" & Format(Now(), "dd/mm/yyyy") I want to filter anything dated today or prior. It's not working - every line is being filtered out. When I drop down the autofilter button for the column, and go to 'Custom' I can see the correct date is in the dialog box, and the filter works when I press OK through it. Anyone know why this isn't working? Cheers Tony |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofilter Criteria not filtering when using NOW()
Still doesn't work.. Same problem..
On 24 Aug, 15:08, "Ron de Bruin" wrote: Try DateSerial Selection.AutoFilter Field:=Range("Note1").Column, Criteria1:="<=" & DateSerial(Year(Date), Month(Date), Day(Date)) -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "bony_tony" wrote in oglegroups.com... Hi, I have this line of code. Selection.AutoFilter Field:=Range("Note1").Column, Criteria1:="<=" & Format(Now(), "dd/mm/yyyy") I want to filter anything dated today or prior. It's not working - every line is being filtered out. When I drop down the autofilter button for the column, and go to 'Custom' I can see the correct date is in the dialog box, and the filter works when I press OK through it. Anyone know why this isn't working? Cheers Tony- Hide quoted text - - Show quoted text - |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofilter Criteria not filtering when using NOW()
Sub AAA()
ActiveSheet.Cells.Select Selection.AutoFilter Field:=Range("Note1").Column, _ Criteria1:="<=" & Now() End Sub worked fine for me. -- Regards, Tom Ogilvy "bony_tony" wrote: Still doesn't work.. Same problem.. On 24 Aug, 15:08, "Ron de Bruin" wrote: Try DateSerial Selection.AutoFilter Field:=Range("Note1").Column, Criteria1:="<=" & DateSerial(Year(Date), Month(Date), Day(Date)) -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "bony_tony" wrote in oglegroups.com... Hi, I have this line of code. Selection.AutoFilter Field:=Range("Note1").Column, Criteria1:="<=" & Format(Now(), "dd/mm/yyyy") I want to filter anything dated today or prior. It's not working - every line is being filtered out. When I drop down the autofilter button for the column, and go to 'Custom' I can see the correct date is in the dialog box, and the filter works when I press OK through it. Anyone know why this isn't working? Cheers Tony- Hide quoted text - - Show quoted text - |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofilter Criteria not filtering when using NOW()
Another attempt -
Sub test() Dim s As String ' asssumes all cells below the header have same date format s = Range("Note1").Offset(1).NumberFormat Selection.AutoFilter Field:=Range("Note1").Column, _ Criteria1:="<=" & Format(Now(), s) End Sub Regards, Peter T "bony_tony" wrote in message ups.com... Still doesn't work.. Same problem.. On 24 Aug, 15:08, "Ron de Bruin" wrote: Try DateSerial Selection.AutoFilter Field:=Range("Note1").Column, Criteria1:="<=" & DateSerial(Year(Date), Month(Date), Day(Date)) -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "bony_tony" wrote in oglegroups.com... Hi, I have this line of code. Selection.AutoFilter Field:=Range("Note1").Column, Criteria1:="<=" & Format(Now(), "dd/mm/yyyy") I want to filter anything dated today or prior. It's not working - every line is being filtered out. When I drop down the autofilter button for the column, and go to 'Custom' I can see the correct date is in the dialog box, and the filter works when I press OK through it. Anyone know why this isn't working? Cheers Tony- Hide quoted text - - Show quoted text - |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofilter Criteria not filtering when using NOW()
I jumped in a bit too quick without fully testing, ignore that. There's a
bit more to it! Ron & Tom, neither of yours work for me either. Regards, Peter T "Peter T" <peter_t@discussions wrote in message ... Another attempt - Sub test() Dim s As String ' asssumes all cells below the header have same date format s = Range("Note1").Offset(1).NumberFormat Selection.AutoFilter Field:=Range("Note1").Column, _ Criteria1:="<=" & Format(Now(), s) End Sub Regards, Peter T "bony_tony" wrote in message ups.com... Still doesn't work.. Same problem.. On 24 Aug, 15:08, "Ron de Bruin" wrote: Try DateSerial Selection.AutoFilter Field:=Range("Note1").Column, Criteria1:="<=" & DateSerial(Year(Date), Month(Date), Day(Date)) -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "bony_tony" wrote in oglegroups.com... Hi, I have this line of code. Selection.AutoFilter Field:=Range("Note1").Column, Criteria1:="<=" & Format(Now(), "dd/mm/yyyy") I want to filter anything dated today or prior. It's not working - every line is being filtered out. When I drop down the autofilter button for the column, and go to 'Custom' I can see the correct date is in the dialog box, and the filter works when I press OK through it. Anyone know why this isn't working? Cheers Tony- Hide quoted text - - Show quoted text - |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofilter Criteria not filtering when using NOW()
If they are dates this is working OK here for column A
And Tom's example will also work. Columns("A").AutoFilter Field:=1, Criteria1:="<=" & DateSerial(Year(Date), Month(Date), Day(Date)) Maybe this is not correct Range("Note1").Column -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Peter T" <peter_t@discussions wrote in message ... I jumped in a bit too quick without fully testing, ignore that. There's a bit more to it! Ron & Tom, neither of yours work for me either. Regards, Peter T "Peter T" <peter_t@discussions wrote in message ... Another attempt - Sub test() Dim s As String ' asssumes all cells below the header have same date format s = Range("Note1").Offset(1).NumberFormat Selection.AutoFilter Field:=Range("Note1").Column, _ Criteria1:="<=" & Format(Now(), s) End Sub Regards, Peter T "bony_tony" wrote in message ups.com... Still doesn't work.. Same problem.. On 24 Aug, 15:08, "Ron de Bruin" wrote: Try DateSerial Selection.AutoFilter Field:=Range("Note1").Column, Criteria1:="<=" & DateSerial(Year(Date), Month(Date), Day(Date)) -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "bony_tony" wrote in oglegroups.com... Hi, I have this line of code. Selection.AutoFilter Field:=Range("Note1").Column, Criteria1:="<=" & Format(Now(), "dd/mm/yyyy") I want to filter anything dated today or prior. It's not working - every line is being filtered out. When I drop down the autofilter button for the column, and go to 'Custom' I can see the correct date is in the dialog box, and the filter works when I press OK through it. Anyone know why this isn't working? Cheers Tony- Hide quoted text - - Show quoted text - |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofilter Criteria not filtering when using NOW()
Being in the US, I don't have the problem. However, in my test, I did change
the format in the key column to dd/mm/yyyy before running the code. In the past, I have had luck with some other users with this problem with regional versions (that use dates starting with day then month) by using the dateserial. So not sure what to tell the OP if he/she continues to fail and has accurately porrayed what he/she is trying to do. -- Regards, Tom Ogilvy "Peter T" wrote: I jumped in a bit too quick without fully testing, ignore that. There's a bit more to it! Ron & Tom, neither of yours work for me either. Regards, Peter T "Peter T" <peter_t@discussions wrote in message ... Another attempt - Sub test() Dim s As String ' asssumes all cells below the header have same date format s = Range("Note1").Offset(1).NumberFormat Selection.AutoFilter Field:=Range("Note1").Column, _ Criteria1:="<=" & Format(Now(), s) End Sub Regards, Peter T "bony_tony" wrote in message ups.com... Still doesn't work.. Same problem.. On 24 Aug, 15:08, "Ron de Bruin" wrote: Try DateSerial Selection.AutoFilter Field:=Range("Note1").Column, Criteria1:="<=" & DateSerial(Year(Date), Month(Date), Day(Date)) -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "bony_tony" wrote in oglegroups.com... Hi, I have this line of code. Selection.AutoFilter Field:=Range("Note1").Column, Criteria1:="<=" & Format(Now(), "dd/mm/yyyy") I want to filter anything dated today or prior. It's not working - every line is being filtered out. When I drop down the autofilter button for the column, and go to 'Custom' I can see the correct date is in the dialog box, and the filter works when I press OK through it. Anyone know why this isn't working? Cheers Tony- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Why is autofilter only filtering up to # of rows in prev column? | Excel Discussion (Misc queries) | |||
| Selection.AutoFilter Field / Criteria = criteria sometimes non-existing on worksheet | Excel Programming | |||
| Autofilter Not Filtering Properly | Excel Discussion (Misc queries) | |||
| AutoFilter only filtering first 1000 | Excel Discussion (Misc queries) | |||
| Filtering out dates using autofilter | Excel Programming | |||
Hybrid Mode
