Auto filter using macro not woking

A

ashish128

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
 
D

Dave Peterson

=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.
 
A

ashish128

=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.












--

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.
 

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

Top