Here's VB code that will let the user pick "(All)" from a dropdown box and
have the filter work like you mentioned.

If Worksheets("Input").Range("E26").Value = "(All)" Then
Selection.AutoFilter _
Field:=2
Else:
Selection.AutoFilter _
Field:=2, _
Criteria1:=ThisWorkbook.Worksheets("Input").Range( "E26").Value, _
Operator:=xlAnd
End If

Hope that makes sense but it worked for me.

"Stefi" wrote:

I'm not sure I understand your problem. Criterium '(All)' is represented in
VBA by no Criteria value:

SourceRange.AutoFilter Field:=7

Regards,
Stefi

€žshiro€� ezt Ã*rta:

Hi Stefi,
The user is not interact directly with the VBA editor.
May be we enable to view the code and change
the code directly,but in my case,the user input about
5 criterias in the worksheets cells by selecting them
from a dropdown list.And I was unable to find the value
that represent criteria '(All)' for the filter criteria.


"Stefi" wrote in message
...
Simply
SourceRange.AutoFilter Field:=7

Recording operations as a macro easily answers most questions of this
kind.
Try it!

Regards,
Stefi

"shiro" ezt Ã*rta:

On auto filter,usually there is a criteria '(All)'.What
is the match word in the vba to represent '(All)' to
show all records?

here is my filter
SourceRange.AutoFilter Field:=7, _
Criteria1:="=" & WS.Range("A10").Value

I tried to add word (All) but it returns no record.
Thnk's

Rgds,

Shiro