In a Selection.AutoFilter Field is there a way to have Criteria1 represent
"all"?

For example,

Selection.AutoFilter Field:=4

alone will represent "all"

and

Selection.AutoFilter Field:=4, Criteria1:=Price

will select based on the price.

I have set up a Userform so that "all" could be represented by "0" in the
string variable Price. But I would preferred to have it represented by "all" in
some form when the textbox is left blank.

I could figure out some code that the ", Criteria1:=Price" is added after
Selection.AutoFilter Field is added but that seems to be going overboard.

Thanks,
Jeff

Jeff,

This seems to work fine:

If Price = "0" Or Price = "" Then
Selection.AutoFilter Field:=4
Else
Selection.AutoFilter Field:=4, Criteria1:=Price
End IF

HTH,
Bernie
Excel MVP


"JeffFinnan" wrote in message ...
In a Selection.AutoFilter Field is there a way to have Criteria1 represent
"all"?

For example,

Selection.AutoFilter Field:=4

alone will represent "all"

and

Selection.AutoFilter Field:=4, Criteria1:=Price

will select based on the price.

I have set up a Userform so that "all" could be represented by "0" in the
string variable Price. But I would preferred to have it represented by "all" in
some form when the textbox is left blank.

I could figure out some code that the ", Criteria1:=Price" is added after
Selection.AutoFilter Field is added but that seems to be going overboard.

Thanks,
Jeff

Bernie,

Yeah, I guess I will have to resort to this type of conditional phrasing. I was
hoping that there might be some simpler trick.

Thanks,
Jeff

If Price = "0" Or Price = "" Then
Selection.AutoFilter Field:=4
Else
Selection.AutoFilter Field:=4, Criteria1:=Price
End IF