snowbounduk
11-23-2010, 05:16 AM
I have the following code which I am using to apply filters to 3 fields. In field 21 I currently have 2 criteria and I need to add a third, PR2 Interim. I have tried adding Criteria3 without success.
I now understand I can only use 2 criteria using my current method and that an Array may be required.
Could anyone advise on how I apply the 3 crieria using an Array?
Thanks in anticipation....
Sub SlipNoIssuePP2()
'
' SlipNoIssuePP2 Macro
'
'
Sheets("Slip No Issue PP2").Select
Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.EntireRow.Delete
Range("A1").Select
Sheets("All MS Checks").Select
Selection.AutoFilter Field:=20, Criteria1:=">0", Operator:=xlAnd
Selection.AutoFilter Field:=10, Criteria1:="<>I*", Operator:=xlAnd
Selection.AutoFilter Field:=21, Criteria1:="PR1", Operator:=xlOr, _
Criteria2:="Pre-PR1"
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("Slip No Issue PP2").Select
Range("A1").Select
ActiveSheet.Paste
Range("A1").Select
Sheets("All MS Checks").Select
Selection.AutoFilter Field:=10
Selection.AutoFilter Field:=21
Selection.AutoFilter Field:=20
Range("A1").Select
End Sub
I now understand I can only use 2 criteria using my current method and that an Array may be required.
Could anyone advise on how I apply the 3 crieria using an Array?
Thanks in anticipation....
Sub SlipNoIssuePP2()
'
' SlipNoIssuePP2 Macro
'
'
Sheets("Slip No Issue PP2").Select
Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.EntireRow.Delete
Range("A1").Select
Sheets("All MS Checks").Select
Selection.AutoFilter Field:=20, Criteria1:=">0", Operator:=xlAnd
Selection.AutoFilter Field:=10, Criteria1:="<>I*", Operator:=xlAnd
Selection.AutoFilter Field:=21, Criteria1:="PR1", Operator:=xlOr, _
Criteria2:="Pre-PR1"
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("Slip No Issue PP2").Select
Range("A1").Select
ActiveSheet.Paste
Range("A1").Select
Sheets("All MS Checks").Select
Selection.AutoFilter Field:=10
Selection.AutoFilter Field:=21
Selection.AutoFilter Field:=20
Range("A1").Select
End Sub