AutoFilter based on 2 Fields and 2 Criteria?

[DISMfish]

Is it possible to AutoFilter based on adjacent cells with complementing
criteria?

For instance, I have values in row x, columns y and z. And I need to
see if x,y < Minimum and x,z > Maximum is this possible?


Here's what I have right now:

'Is Val_Min between limits? (Column 17)
Sheets(sName).Cells(17, 1).AutoFilter Field:=17, _
Criteria1:=">=" & Min_Limit, Operator:=xlAnd, Criteria2:="<=" &
Max_Limit

'Is Val_Max between limits? (Column 18)
Sheets(sName).Cells(18, 1).AutoFilter Field:=18, _
Criteria1:=">=" & Min_Limit, Operator:=xlAnd, Criteria2:="<=" &
Max_Limit

'Is Val_Min below Min_Limit AND Val_Max above Max_Limit? (Column 17
and 18)
????


Sheets(sName).UsedRange.SpecialCells(xlCellTypeVisible).Copy

Sheets.Add().Name = strName
ActiveSheet.Paste



Thanks,
Logan

 

[davegb]

Is it possible to AutoFilter based on adjacent cells with complementing
criteria?

For instance, I have values in row x, columns y and z. And I need to
see if x,y < Minimum and x,z > Maximum is this possible?


Here's what I have right now:

'Is Val_Min between limits? (Column 17)
Sheets(sName).Cells(17, 1).AutoFilter Field:=17, _
Criteria1:=">=" & Min_Limit, Operator:=xlAnd, Criteria2:="<=" &
Max_Limit

'Is Val_Max between limits? (Column 18)
Sheets(sName).Cells(18, 1).AutoFilter Field:=18, _
Criteria1:=">=" & Min_Limit, Operator:=xlAnd, Criteria2:="<=" &
Max_Limit

'Is Val_Min below Min_Limit AND Val_Max above Max_Limit? (Column 17
and 18)
????


Sheets(sName).UsedRange.SpecialCells(xlCellTypeVisible).Copy

Sheets.Add().Name = strName
ActiveSheet.Paste



Thanks,
Logan

In my limited experience, you need a Custom Filter to filter on more
than one field at a time.

Hope this helps in your world.

 

[DISMfish]

How do you make a custom filter in vb?

 

[davegb]

How do you make a custom filter in vb?

Best way to start is to make one while while you record a macro, then
modify it to your needs. You don't have to actually create the Criteria
fields, they can already be there and you just select them while
recording the macro.

Hope this helps.

 

[DISMfish]

Thanks. It looks like I can't use it anyway. I need to sort by
several fields and the autofilter will only AND them to the visible
list if true. I need to OR them to the visible list for each
autofilter run.

 

[Wendell A. Clark]

I had the similiar problem usinghe advance filter I set the criteria range
up like this Note it is 3 columns:

A B C
1 CORRECTION_NUM M0090_INFO_COMPLETED_DT M0090_INFO_COMPLETED_DT

2 <>99 >01/01/2005
<12/31/2005

3 01/01/2005
12/31/2005


The key here is in columns b c rows 2 and 3 function as an 'OR'

I got my help from http://www.contextures.com/xladvfilter02.html as directed
by these illustrious teachers to the world

Hope it helps

--
Wendell A. Clark, BS
-------------------------------------

CONFIDENTIALITY NOTICE: This e-mail communication and any attachments may
contain confidential and privileged information for the use of the
designated recipients named above. If you are not the intended recipient,
please notify us by reply e-mail. You are hereby notified that you have
received this communication in error and that any review, disclosure,
dissemination, distribution or copying of it or its contents is prohibited.
If you have received this communication in error, please destroy all copies
of this communication and any attachments. Contact the sender if it
continues.