using Criteria with two possible values

[DKY]

its for deleting rows with that text in the column. it looks like this
Dim rng As Range, rng1 As Range
Range("J:J").AutoFilter _
Field:=1, Criteria1:="not in system"
On Error Resume Next
Set rng1 = Range("J2:J65536").SpecialCells(xlVisible)
On Error GoTo 0
If Not rng1 Is Nothing Then
rng1.EntireRow.Delete
End If
Range("J1").CurrentRegion.AutoFilter

So, will this work?

Dim rng As Range, rng1 As Range
Range("J:J").AutoFilter _
Field:=1, Criteria1:="=not in system", _
Operator:=xlOr, _
Criteria2:="=NOT OKC"
On Error Resume Next
Set rng1 = Range("J2:J65536").SpecialCells(xlVisible)
On Error GoTo 0
If Not rng1 Is Nothing Then
rng1.EntireRow.Delete
End If
Range("J1").CurrentRegion.AutoFilter

or do I need a Field:=2 before the second criteri

 

[JulieD]

Hi DKY

the Field:=1
means the first field in your data area
so, no, from what i can see you're okay.

but why don't you record a macro doing exactly this (using CUSTOM to choose
both criteria - make sure you populate all four boxes and choose OR as the
comparison operator) and see the code which is generated.

Cheers
JulieD

 

[Bernie Deitrick]

DKY,

Yes, your code should work as modified.
No, you don't need a Field2 for the second criteria.

HTH,
Bernie
MS Excel MVP