Auto filter using macro not woking
 

Dear All,

I have a column of dates, past & future. It may or may not have
current date. I am trying to auto filter to view only those rows that
contains dates of future (as of current date) . Manually this is fine
but when I try to recorded macro this as macro and edit it to modify
condition as

Selection.AutoFilter Field:=27, Criteria1:="=" & today(),
Operator:=xlAnd

It gives an error, "Compile Error: Sub or function not defined"

Following is my entire code

Sub accrual_swap()
'
' accrual_swap Macro
' Macro recorded 6/6/2007 by Ashish Sharma
'

'

Range("AA1").Select
Rows("1:1").Select
Range("AA1").Activate
Selection.Insert Shift:=xlDown
Selection.AutoFilter
Range("AA1").Select
Selection.AutoFilter Field:=27, Criteria1:="=" & today(),
Operator:=xlAnd
' Following was original line of code
' Selection.AutoFilter Field:=27, Criteria1:="6/4/2007",
Operator:=xlAnd
Columns("A:Y").Select
Range("Y1").Activate
Selection.Delete Shift:=xlToLeft
Columns("C:L").Select
Selection.Delete Shift:=xlToLeft
Range("A21:A79").Select
End Sub

Please help

Auto filter using macro not woking
 

=today() is a worksheet function--you'd use it in a cell when you're writing a
formula.

I'd try this:
Selection.AutoFilter Field:=27, Criteria1:="=" & date

but filtering dates can be tricky.

This may work if the first doesn't:
Selection.AutoFilter Field:=27, Criteria1:="=" & clng(date)

And one more--just in case:
Selection.AutoFilter Field:=27, Criteria1:="=" & format(date,"m/d/yyyy")

Match the date format to what you use in the worksheet.


ashish128 wrote:

Dear All,

I have a column of dates, past & future. It may or may not have
current date. I am trying to auto filter to view only those rows that
contains dates of future (as of current date) . Manually this is fine
but when I try to recorded macro this as macro and edit it to modify
condition as

Selection.AutoFilter Field:=27, Criteria1:="=" & today(),
Operator:=xlAnd

It gives an error, "Compile Error: Sub or function not defined"

Following is my entire code

Sub accrual_swap()
'
' accrual_swap Macro
' Macro recorded 6/6/2007 by Ashish Sharma
'

'

Range("AA1").Select
Rows("1:1").Select
Range("AA1").Activate
Selection.Insert Shift:=xlDown
Selection.AutoFilter
Range("AA1").Select
Selection.AutoFilter Field:=27, Criteria1:="=" & today(),
Operator:=xlAnd
' Following was original line of code
' Selection.AutoFilter Field:=27, Criteria1:="6/4/2007",
Operator:=xlAnd
Columns("A:Y").Select
Range("Y1").Activate
Selection.Delete Shift:=xlToLeft
Columns("C:L").Select
Selection.Delete Shift:=xlToLeft
Range("A21:A79").Select
End Sub

Please help

--

Dave Peterson

Auto filter using macro not woking
 

On Jun 6, 5:44 pm, Dave Peterson wrote:
=today() is a worksheet function--you'd use it in a cell when you're writing a
formula.

I'd try this:
Selection.AutoFilter Field:=27, Criteria1:="=" & date

but filtering dates can be tricky.

This may work if the first doesn't:
Selection.AutoFilter Field:=27, Criteria1:="=" & clng(date)

And one more--just in case:
Selection.AutoFilter Field:=27, Criteria1:="=" & format(date,"m/d/yyyy")

Match the date format to what you use in the worksheet.





ashish128 wrote:

Dear All,

I have a column of dates, past & future. It may or may not have
current date. I am trying to auto filter to view only those rows that
contains dates of future (as of current date) . Manually this is fine
but when I try to recorded macro this as macro and edit it to modify
condition as

Selection.AutoFilter Field:=27, Criteria1:="=" & today(),
Operator:=xlAnd

It gives an error, "Compile Error: Sub or function not defined"

Following is my entire code

Sub accrual_swap()
'
' accrual_swap Macro
' Macro recorded 6/6/2007 by Ashish Sharma
'

'

Range("AA1").Select
Rows("1:1").Select
Range("AA1").Activate
Selection.Insert Shift:=xlDown
Selection.AutoFilter
Range("AA1").Select
Selection.AutoFilter Field:=27, Criteria1:="=" & today(),
Operator:=xlAnd
' Following was original line of code
' Selection.AutoFilter Field:=27, Criteria1:="6/4/2007",
Operator:=xlAnd
Columns("A:Y").Select
Range("Y1").Activate
Selection.Delete Shift:=xlToLeft
Columns("C:L").Select
Selection.Delete Shift:=xlToLeft
Range("A21:A79").Select
End Sub

Please help

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Thanks Friend,

I tried the second otion

Selection.AutoFilter Field:=27, Criteria1:="=" & clng(date)

and it worked for me.

Thanks a lot.