Autofilter
Just to reinforce, from "Filter a Range" in the Excel help (written about
autofilters), in the Notes section:
=====================================
a.. When you apply a filter to a column, the only filters available for
other columns are the values visible in the currently filtered range.
=====================================
You can not alter how the filter operates just because you are using VBA.
--
Regards,
Tom Ogilvy
"Tom Ogilvy" wrote in message
...
The criteria will not be judged independently. If you apply the criteria
to
column 14, then you go to column 18 and select the dropdown, the only
options will be for those values in the currently visible rows. Criteria
are cumulative in an Autofilter. so criteria in separate columns are
implicitly joined with and AND condition.
--
Regards,
Tom Ogilvy
"ben" (remove this if mailing direct) wrote in message
...
if that is so you will need two seperate line statements
Selection.AutoFilter Field:=14, Criteria1:="<0", Operator:=xlAnd
Selection.AutoFilter Field:=18, Criteria1:="<8", Operator:=xlAnd
in this case excel will ignore the operator because there is no second
condition
however both criteria will be judged independently and you should
recieve
the results you want
--
When you lose your mind, you free your life.
"ben" wrote:
do you want it to
show these rows
1 1 1 1 1 1 1 1 1 1 1 1 1 -1 1 1 1
1 1 1 1 1 1 1 1 1 1 1 1 1 -1 1 1 9
but not these ones?
1 1 1 1 1 1 1 1 1 1 1 1 1 -1 1 1 8
1 1 1 1 1 1 1 1 1 1 1 1 1 5 1 1 9
--
When you lose your mind, you free your life.
"John" wrote:
Ben,
would you mind posting what command would work? If I use
Operator:=xlOr or
Operator:=xlAnd ...it will not do what I am wanting. I want to show
rows
that don't equal 8 and are less than zero.
Thanks again.
"ben" wrote:
because your criteria says OR, excel will hide the row if either
of
those
conditions is true, not if BOTH of them are true, therfore hides
when either
criteria is met
--
When you lose your mind, you free your life.
"John" wrote:
If I want to filter the data so that any row that has a value
greater than 0
in column 14 OR does not have a value of 8 in column 18... why
does this code
not work... It gives me only those rows with values less than
zero
in column
14 and hides every row that does not equal 8 in column 18!
Sheets("CORPS").Select
Selection.AutoFilter Field:=14, Criteria1:="<0",
Operator:=xlOr
Selection.AutoFilter Field:=18, Criteria1:="<8"
Thanks for the help
|