The only operator that I saw that was necessary was in the first filter. The
others weren't needed.

But you could use the same kind of code to point at any cell in any worksheet.

If you mean the comparison operator ( may be = or < or <= or =), you could
concatenate that, too:

with .range("a2")
if isempty(.value) then
'skip it
else
Selection.AutoFilter Field:=20, Criteria1:="" & .value
end if
end with

Becomes:

if isempty(range("a2").value) then
'skip it
else
Selection.AutoFilter Field:=20, _
Criteria1:=.range("A99").value & .range("a2").value
'change the addresses to what you need
end if
end with

Marie Bayes wrote:

Hi Dave

I'm not sure this is what I'm looking for, sorry if i'm being dumb, but the
criteria and operator for each of the 3 autofilters will change or be empty,
so the users input the criteria and operator that they require for the
autofilter into a cell in sheet A, this then needs to be input into the code
to automatically filter the data in "Store Data". So, using your code, if
the data in "a1" is empty, that's great, but if it's not there'll be criteria
and an operator in cells a1 and a2.

Do you have any ideas if this can be done?

Thanks in advance.

"Dave Peterson" wrote:

Maybe...

Sub TEST()
Sheets("Store Data").Select
with worksheets("sheet99")

with .range("a1")
if isempty(.value) then
'skip it
else
Selection.AutoFilter Field:=8, Criteria1:=.value, _
Operator:=xlTop10Items
end if
End with

with .range("a2")
if isempty(.value) then
'skip it
else
Selection.AutoFilter Field:=20, Criteria1:="" & .value
end if
end with

with .range("a3")
if isempty(.value) then
'skip it
else
Selection.AutoFilter Field:=21, Criteria1:="" & .value
end if
end with
end with
End Sub

Maybe you can pick out something worth saving.

Marie Bayes wrote:

I don't know if anyone can help. I need to create some code that operates
autofilter, I don't have a problem doing this:
Sub TEST()
Sheets("Store Data").Select
Selection.AutoFilter Field:=8, Criteria1:="10", Operator:=xlTop10Items
Selection.AutoFilter Field:=20, Criteria1:="30", Operator:=xlAnd
Selection.AutoFilter Field:=21, Criteria1:="50", Operator:=xlAnd
End Sub
However, the criteria and operator will change everytime, the user will be
putting the criteria and operator into cells on another sheet and I need the
code to pick up this data from the cells. Plus, there will sometimes be only
1, 2 or 3 critieria, ie, not all three selections will be made everytime.

Is this at all possible? Thanks in advance.

--

Dave Peterson


--

Dave Peterson