More Than 2 Criteria

[honkin]

hi

I recorded 3 macros so I could see the code for each and hopefully compile something which will filter for all 3, as the standard Excel filter will only allow for 2.

I am trying to filter to only leave rows with a zero, a blank or that are >=30

The code for code for each came up as follows, yet the column is not number 16, so am not sure the Field:=16 is correct. The column is AN, which is number 40, though it would be better if it simply worked on the active column.

The maximum number in the column would be 100, so from blank and zeroes all the way up to 100

The individual codes are as follows:-

[SIZE=1]    ActiveSheet.Range("$Y$1:$BS$633").AutoFilter Field:=16, Criteria1:="="[/SIZE]
[SIZE=1]    Selection.AutoFilter[/SIZE]

[SIZE=1]    ActiveSheet.Range("$Y$1:$BS$633").AutoFilter Field:=16, Criteria1:="0"[/SIZE]
[SIZE=1]    Selection.AutoFilter[/SIZE]

[SIZE=1]ActiveSheet.Range("$Y$1:$BS$633").AutoFilter Field:=16, Criteria1:=">=30"[/SIZE]
[SIZE=1]Selection.AutoFilter[/SIZE]

I assume the Record Macro function simply determines the number of rows, but this will possibly be problematic in the future as the macro is to be used on any file and there will be varying numbers of rows. I assume the below code would solve this by selecting the column with the active cell.

ActiveSheet.Columns(ActiveCell.Column).EntireColumn.Select

So how would it all go together where the active cell's column is selected and filtered for the 3 criteria above?

Thanks so much in advance

 

[James006]

Well let's concentrate on Column AO ...

What does this Column hold ?

What are exactly your three criteria ?

Are your Blanks ...actual Blanks ... ?

 

[James006]

After a couple of tests ... would suggest the following:

In Column BT ... cell BT2 =OR(AO2="",AO2=0,AO2>=30)

Then you can Filter on column 72 for TRUE ...

Hope this will help

 

[honkin]

Well let's concentrate on Column AO ...

What does this Column hold ?

What are exactly your three criteria ?

Are your Blanks ...actual Blanks ... ?

Thanks for your reply, James006

AO holds numbers and blanks and yes, the blanks are actually blank cells

cheers

 

[honkin]

After a couple of tests ... would suggest the following:

In Column BT ... cell BT2 =OR(AO2="",AO2=0,AO2>=30)

Then you can Filter on column 72 for TRUE ...

Hope this will help

ah, now you're onto something. It's not ideal, as I still have to manually prepare that column, which is what the macro was trying to avoid, but the results match between the macro and manual filtering, so thanks very much

 

[James006]

You are welcome

 

[James006]

Just out of curiosity ...

Could you turn on your macro recorder ... and perform your ' manual ' filtering only on Column AO ...

Would like to see the ' VBA translation ' of this specific combination ...

Thanks ...

 

[honkin]

Just out of curiosity ...

Could you turn on your macro recorder ... and perform your ' manual ' filtering only on Column AO ...

Would like to see the ' VBA translation ' of this specific combination ...

Thanks ...

Ran the macro recorder and this is what it looks like.

Sub Jockey()
'
' Jockey Macro
'


'
    Range("AP5").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$BU$469").AutoFilter Field:=41, Criteria1:=">=30"
End Sub

Apart from the range reference, it is pretty much the same. Just not sure what would stop this line from working when all the others are fine

.AutoFilter Field:=41, Criteria1:=Array(">=30", "0", "="), Operator:=xlFilterValues

cheers

 

[James006]

Thanks a lot ...

It seems the Array can accept many criteria ... but does not ' digest ' comparison operators ...:wink: