Why is my Filter Macro not Filtering Correctly?

[rob51852]

Hi,

I am trying to put together a simple macro to filter a table for rows containing today's date (column 1) and where the value in column 5 is greater than 5. However, when I run it all rows disappear even though there are many that meet the criteria.

Can anyone see what the problem is?

Thanks

Sub Filter()
ActiveSheet.ListObjects("TTable").Range.AutoFilter Field:=1, Criteria1:=Array(Date), Operator:=xlFilterValues
ActiveSheet.ListObjects("TTable").Range.AutoFilter Field:=5, Criteria2:=">5", Operator:=xlFilterValues
End Sub

 

[RoryA]

Dates and filters can be problematic in code. It's usually safer to do something like this:

Sub Filter()
With ActiveSheet.ListObjects("TTable").Range
   .AutoFilter Field:=1, Criteria1:=">=" & CDbl(Date), Operator:=xlAnd, Criteria2:="<=" & CDbl(Date)
   .AutoFilter Field:=5, Criteria1:=">5"
End With
End Sub

 

[rob51852]

Thanks Rory. I still get the same result unfortunately.

 

[RoryA]

Are you sure there aren't any time parts in the date column that are hidden by formatting? If there are, use:

Sub Filter()
With ActiveSheet.ListObjects("TTable").Range
   .AutoFilter Field:=1, Criteria1:=">=" & CDbl(Date), Operator:=xlAnd, Criteria2:="<" & CDbl(Date) + 1
   .AutoFilter Field:=5, Criteria1:=">5"
End With
End Sub

 

[Marc L]

Hi,​

​

do it manually and once it works, cancel the filter and activate the Macro Recorder and redo the same operation in order to compare with the generated code …​

 

[rob51852]

Thanks again Rory. For some reason the filter is greyed out. I've tried formatting the column as dates but it's still grey

 

[RoryA]

What exactly is greyed out?

 

[rob51852]

I've closed and reopened the workbook and now it just give sort A to Z as the option. It's the same when I go to the Data tab and try sorting from there

 

[RoryA]

It looks like you have some non-date entries in there, since you have Text filters as an option, not Date filters.

 

[rob51852]

Strange one. There's no text in there. I'll come up with a workaround.

Thanks for all your help Rory.