Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel 2007 autofilter change to 2003 autofilter functionality? | Excel Discussion (Misc queries) | |||
2007 excel autofilter back to 2003 autofilter? | Excel Discussion (Misc queries) | |||
2007 excel autofilter change back to 2003 autofilter? | Excel Discussion (Misc queries) | |||
2007 Autofilter worse than 2003 Autofilter | Excel Discussion (Misc queries) | |||
How to Sort within AutoFilter with Protection on (and AutoFilter . | Excel Discussion (Misc queries) |