Autofilter

[Guest]

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

 

[Guest]

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

 

[Guest]

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.

 

[Nigel]

Greater than 0 means ALL values above 0 so use ">0" not "<0"
'OR does not' implies 'AND equals' so use.....unless I got your logic wrong!

Sheets("CORPS").Select
Selection.AutoFilter Field:=14, Criteria1:=">0", Operator:=xlAnd
Selection.AutoFilter Field:=18, Criteria1:="=8"

 

[Guest]

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

 

[Nigel]

I did get it wrong - your last post says "I want to show rows that don't
equal 8 and are less than zero"

so use.....

Sheets("CORPS").Select
Selection.AutoFilter Field:=14, Criteria1:="<0", Operator:=xlAnd
Selection.AutoFilter Field:=18, Criteria1:="<>8"

 

[Guest]

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

 

[Guest]

Ben,

Under your heading of 'show these rows' I would like to show the first two
since the 8th column has a negative number. And under the heading of 'but
not these ones'... assuming your 17h column was meant to be the 18th
column... I would like to show the first row since it has a negative number
in column 8, and also show the second one because it has a number other than
8 in column 18.

 

[Guest]

Nigel, that gives me rows that have a column 8 value less than zero and a
column 18 that doesn't equal 8... I would like it to show, for example, if a
row had a postive value in column 8 and a value of 7 in column 18 as well.

 

[Guest]

apologies if this is unclear...

Nigel, that gives me rows that have a column 8 value less than zero and a
column 18 that doesn't equal 8... I would like it to show, for example, if a
row had a postive value in column 8 and a value of 7 in column 18 as well.

 

[Tom Ogilvy]

Criteria between each column Creates a LOGICAL AND condition.

You would need to use a formula in a dummy column to establish the OR
relationship and then filter on that column.

Or use an Advanced filter.

 

[Tom Ogilvy]

since the 8th column has a negative number.

the eight column has a positive 1 in all cases.

 

[Tom Ogilvy]

Where did column 14 go?

--
Regards,
Tom Ogilvy

apologies if this is unclear...

 

[Tom Ogilvy]

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.

 

[Guest]

Apologies... I meant column 14

Nigel, that gives me rows that have a column 14 value less than zero and a
column 18 that doesn't equal 8... I would like it to show, for example, if a
row had a postive value in column 14 and a value of 7 in column 18 as well.

 

[Tom Ogilvy]

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.

 

[Guest]

Yea, I was working on that Tom... I wasn't sure if it could be done the other
way. I guess I wasn't making myself clear.