Re: Autofilter

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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" <twogilvy@xxxxxxx> wrote in message
news:OnVRtz9hFHA.1044@xxxxxxxxxxxxxxxxxxxxxxx
> 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" <bw_rgb@xxxxxxxxxxx(remove this if mailing direct)> wrote in message
> news:DF2489DB-22A0-4657-AF0C-81CE52611C92@xxxxxxxxxxxxxxxx
> > 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
>
>


.

Quantcast