Autofilter question

[Chad]

I have an autofilter with a column of data that includes operators such as >
and <.

For example, one of the records would read "> 60 days past due"

How would I go about coding the criteria to show the records that display ">
60 days past due"?

Thanks,
Chad

 

[Gord Dibben]

Macro recorder returns this.

Selection.AutoFilter Field:=1, Criteria1:="=> 60 days past due"


Gord Dibben MS Excel MVP

 

[OssieMac]

Hi chad,

I am assuming that you tried recording this and while the filter worked in
the interactive mode; when you run the code it does not work. The answer is
to insert a leading equal sign.

"=>60 days past due"

A tip for these things is the following code. Set the filter then run the
code and you will be able to see the criteria that needs to be used for the
code.

Dim strCriteria

With Worksheets("Sheet1")
If .AutoFilterMode Then
With .AutoFilter.Filters(2)
If .On Then strCriteria = .Criteria1
End With
End If
End With

MsgBox strCriteria

 

[Gord Dibben]

Ossie

When I record, Excel adds the appropriate "=>60 days past due"


Gord

 

[OssieMac]

In Excel 2007 the macro recorder does not return the equal sign. The
following is what I get with the macro recorder. I have not tested in any
other versions.

ActiveSheet.Range("$A$1:$B$835").AutoFilter Field:=2, Criteria1:= _
"> 60 days past due"

And of course the above does not work without the leading = sign when the
code is run.

 

[Gord Dibben]

My testing was on 2003.

I have since tested on 2007 and found that you get different results
depending upon how you filter.

Selecting from the list you get

ActiveSheet.Range("$A$1:$A$22").AutoFilter Field:=1, Criteria1:=">60 days"

Selecting from Custom>Equals you get

ActiveSheet.Range("$A$1:$A$22").AutoFilter Field:=1, Criteria1:="=>60 days"

2003 treats both methods the same and returns in both cases

ActiveSheet.Range("$A$1:$A$22").AutoFilter Field:=1, Criteria1:="=>60 days"



Gord