Custom Filter macro not working

[Excelophobe]

Hi all - please help.

I need a macro that filters a table based on whether the date in the DATE column is equal to today's date or in the future.

Essentially what i need to do is to filter out all entries that are due by today's date or in future, and delete them. What i should be left with are all entries from yesterday and further back in time.

I tried to record a simple macro that selects Custom Filter on DATE column (column 4) and selects "is after or equal to" and today's date from the calendar. Today's date has to be selected/automated as this is for a recurring report that is to be produced on multiple days of the week.

    ActiveSheet.ListObjects("Table6").Range.AutoFilter Field:=4, Criteria1:= _
        ">=24/04/2015", Operator:=xlAnd

This code does not return the required data. Even though i recorded the correct procedure while recording the macro, the macro itself does not pick up what options i have selected in Custom Filter.

Please help!

Best regards,

E.

 

[Deepak]

The issue occurred due to date format.

Try this.

Sub Macro2() 
ActiveSheet.ListObjects("Table6").Range.AutoFilter Field:=4, Criteria1:= _
        ">=" & DateSerial(2015, 4, 24)
End Sub

 

[NARAYANK991]

Hi ,

I think the problem is the addition of the Operator parameter , since you are not using 2 criteria ; an Operator parameter is required only when you are using multiple criteria.

Without it , the following should work :

ActiveSheet.ListObjects("Table6").Range.AutoFilter Field:=4, Criteria1:= _
">=24/04/2015"

Of course , I assume your date format is dd/mm/yyyy

If you want the filter to be related to today's date , you can use :

ActiveSheet.ListObjects("Table6").Range.AutoFilter Field:=4, Criteria1:= _
">=" & Date

where Date is a VBA function which returns today's date.

Narayan

 

[Deepak]

Hi,

Here's no role of "Operator:=xlAnd" either it be or not.

Date format must be same as per system date format.
ActiveSheet.ListObjects("Table6").Range.AutoFilter Field:=4, Criteria1:= _
">=24/04/2015", Operator:=xlAnd

OP code failed in due course his date format doesn't match with the system date format. In a workaround following will also do the job.

ActiveSheet.ListObjects("Table6").Range.AutoFilter Field:=4, Criteria1:= _
">=24/April/2015", Operator:=xlAnd

where system date format is "DD/MMM/YY"

However ">=" & Date or ">=" & DateSerial(2015, 4, 24) should be used to get rid of the error.