Deleting rows with multiple condition

[jqzhang]

Could you check for me see why the second condition not working:

Sub ConditionalDeleteRows()
On Error Resume Next
Range("A1").AutoFilter Field:=7, Criteria1:="<9000", Operator:=xlOr, Criteria2:=">16000"
Range("B1").AutoFilter Field:=8, Criteria1:="<800", Operator:=xlOr, Criteria2:=">1000"
If Err = 0 Then _
Range("A2", Range("A65536").End(xlUp)).SpecialCells(xlCellTypeVisible).EntireRow.Delete
On Error GoTo 0
ActiveSheet.AutoFilterMode = False
End Sub

Thanks.
jqzhang

 

[AnotherHiggins]

What are you expecting this to do that it doesn't? In fact, if you just state what you are trying to accomplish, that would be helpful.

[tt]_____
[blue]-John[/blue]
[/tt][red]Quidquid latine dictum sit, altum viditur[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[jqzhang]

Sorry but I wasn't specific enough.
I want to delete the rows in which Field 7 has values greater than 16k or less than 9k; and Field 8 has values lower than 800 or greater than 1000.
Hope have made myself clear.
Thanks.
jqzhang

 

[PHV]

And this ?
ActiveSheet.AutoFilterMode = False
Range("A1:A8").AutoFilter Field:=7, Criteria1:="<9000", Operator:=xlOr, Criteria2:=">16000"
Range("A1:A8").AutoFilter Field:=8, Criteria1:="<800", Operator:=xlOr, Criteria2:=">1000"
If Err = 0 Then _
Range("A2", Range("A65536").End(xlUp)).SpecialCells(xlCellTypeVisible).EntireRow.Delete

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jqzhang]

PHV,
I got 'Run-time error 1004' using your code if
Range("A1:A8").AutoFilter ...
If I change to
Range("A1").AutoFilter ... then the error is gone but the data I got is not clean: Col. 7 ends up with most of the rows satisfy the condition, but a few not; the same is true for Col. 8.
However, if I do it separately: just run either one of them:
...
Range("A1:A8").AutoFilter Field:=7, Criteria1:="<9000", Operator:=xlOr, Criteria2:=">16000"
Range("A1:A8").AutoFilter Field:=8, Criteria1:="<800", Operator:=xlOr, Criteria2:=">1000"
...

then the data come out perfectly.
This is the same situation as I submitted the thread.
I guess VBA got confused by the conditions.
I'm thinking it should be like the first RANGE statement AND the second one, not the first OR the second (I'm not talking about Operator:=xlOr). Does VBA have any syntax like that? I don't think so.
Thanks again.
jqzhang

 

[PHV]

Sorry for the typo, And this ?
Range("A1:H1").AutoFilter Field:=7, Criteria1:="<9000", Operator:=xlOr, Criteria2:=">16000"
Range("A1:H1").AutoFilter Field:=8, Criteria1:="<800", Operator:=xlOr, Criteria2:=">1000"

Or this ?
Range("G1").AutoFilter Field:=1, Criteria1:="<9000", Operator:=xlOr, Criteria2:=">16000"
Range("H1").AutoFilter Field:=1, Criteria1:="<800", Operator:=xlOr, Criteria2:=">1000"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[AnotherHiggins]

jqzhang,

Maybe I'm missing what you are trying to accomplish, but tell me if this is right:

You want to delete all records where column G (field 7) is not between 9000 and 16000. You also want to delete all records where column H (field 8) is not between 800 and 1000.

If that is what you want, then what about situations where column G is 11000 (meets first criteria) but column H is 700 (does not meet second criteria)? With your present tactic, you won't find and delete these records.

[tt]_____
[blue]-John[/blue]
[/tt][red]Quidquid latine dictum sit, altum viditur[/red]

Help us help you. Please read FAQ181-2886 before posting.