Autofilter
 

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

Autofilter
 

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

Autofilter
 

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

Autofilter
 

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"


--
Cheers
Nigel



"John" wrote in message
...
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

Autofilter
 

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

Autofilter
 

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"




--
Cheers
Nigel



"John" wrote in message
...
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

Autofilter
 

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

Autofilter
 

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.

"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

Autofilter
 

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.

"Nigel" wrote:

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"




--
Cheers
Nigel



"John" wrote in message
...
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

Autofilter
 

apologies if this is unclear...

"John" wrote:

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.

"Nigel" wrote:

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"




--
Cheers
Nigel



"John" wrote in message
...
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

Autofilter
 

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.

--
Regards,
Tom Ogilvy

"John" wrote in message
...
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

Autofilter
 

since the 8th column has a negative number.

the eight column has a positive 1 in all cases.

--
Regards,
Tom Ogilvy

"John" wrote in message
...
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.

"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

Autofilter
 

Where did column 14 go?

--
Regards,
Tom Ogilvy

"John" wrote in message
...
apologies if this is unclear...

"John" wrote:

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.

"Nigel" wrote:

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"




--
Cheers
Nigel



"John" wrote in message
...
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

Autofilter
 

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

Autofilter
 

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" wrote:

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.

--
Regards,
Tom Ogilvy

"John" wrote in message
...
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

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

Autofilter
 

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.

"Tom Ogilvy" wrote:

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.

--
Regards,
Tom Ogilvy

"John" wrote in message
...
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