Help on AutoFilter



In autofilter method (expression.AutoFilter(Field, Criteria1, Operator,
Criteria2, VisibleDropDown)), Criteria1 is Optional Variant. The
criteria (a string; for example, "101")

Problem I encountered:
Selection.AutoFilter Field:=2, Criteria1:=date_limit, Operator:=xlAnd

where date_limit is a string variable.

But I always get Run-time error "1004". Autofilter method of Range
class failed.

Can I use a string variable instead of a direct string value like

What am I doing wrong?



Bob Phillips


It is not you, it is the way Autofilter (does not!) work. To my mind, as the
target cells are dates, you should be able to use a date, but Autofilter
works on strings, so you think okay let's create a date string, but this is
where vthe problem starts.

The obvious solution is to try and format the date in the same way as the
data in the list, like so

Dim string_value As String
string_value = "01-Jan-2005"
Selection.AutoFilter Field:=1, _
Criteria1:=Format(string_value, "dd-mmm")

but this does not work.

Believe it or not, this kludge does work

Dim string_value As String
string_value = "01-Jan-2005"
Selection.AutoFilter Field:=1, _
Criteria1:=">=" & Format(string_value, "dd-mmm"), _
Operator:=xlAnd, _
Criteria2:="<=" & Format(string_value, "dd-mmm")



(remove nothere from the email address if mailing direct)

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