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

 

[honkin]

Or can I simply do something like this to facilitate the 3 criteria?

I have all the columns in this code selected in a range ("A:BS") because there are other filters, though the AN column is the only one with more than 2 criteria. I have removed the code for the other filters as they all work fine.

Sub Debut()
'
' Debut Macro
'


'
    Selection.AutoFilter
    Selection.AutoFilter
    Columns("A:BS").Select
    Selection.AutoFilter
    Range("A1").Select
    ActiveSheet.Range("$A$1:$BS$633").AutoFilter Field:=40, Criteria1:=">=30"
    ActiveSheet.Range("$A$1:$BS$633").AutoFilter Field:=40, Criteria1:="0"
    ActiveSheet.Range("$A$1:$BS$633").AutoFilter Field:=40, Criteria1:="="
End Sub

I would prefer not to have a range in the active sheet, as each sheet the macro is used on will have varying numbers of rows, so would prefer it to just cover ALL rows.

 

[James006]

Hello,

You can simplify your macro

Sub Debut()    
    With ActiveSheet.Range("$A$1:$BS$633")
        .AutoFilter Field:=40, Criteria1:=">=30"
        .AutoFilter Field:=40, Criteria1:="0"
        .AutoFilter Field:=40, Criteria1:="="
   End With
End Sub

Hope this will help

 

[honkin]

Hello,

You can simplify your macro

Sub Debut()    
    With ActiveSheet.Range("$A$1:$BS$633")
        .AutoFilter Field:=40, Criteria1:=">=30"
        .AutoFilter Field:=40, Criteria1:="0"
        .AutoFilter Field:=40, Criteria1:="="
   End With
End Sub

Hope this will help

Thanks so much James006

I would prefer not to put a range in at all, as this limits it to 633 rows, but there will be times there are more rows. Would it just be changing that range to ("A1") for it to facilitate that?

cheers

 

[James006]

Hello,

For a variable range ...

Sub Debut()
Dim last As Long
last = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).row
    With ActiveSheet.Range("A1:BS" & last)
        .AutoFilter Field:=40, Criteria1:=">=30"
        .AutoFilter Field:=40, Criteria1:="0"
        .AutoFilter Field:=40, Criteria1:="="
   End With
End Sub

Hope this will help

 

[honkin]

Hello,

For a variable range ...

Sub Debut()
Dim last As Long
last = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).row
    With ActiveSheet.Range("A1:BS" & last)
        .AutoFilter Field:=40, Criteria1:=">=30"
        .AutoFilter Field:=40, Criteria1:="0"
        .AutoFilter Field:=40, Criteria1:="="
   End With
End Sub

Hope this will help

Hi James006

Thanks so much. That seemed to work fine on its own, but when I combine it with the other criteria, I get different results when I run the macro than when I just do the filters manually

Here is the full code. Is it possibly better to be using arrays for the filters which use multiple criteria?

Sub Debut()
'
' DebutDestroyer Macro
' This macro will filter for VDW Win %, Class, VDW Form, RP Rating, J&T, Race Type and Contender
'
Dim last As Long
last = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    With ActiveSheet.Range("A1:BU" & last)
    .AutoFilter Field:=7, Criteria1:="Flat Turf"
    .AutoFilter Field:=7, Criteria1:="NH Flat"
    .AutoFilter Field:=7, Criteria1:="Hurdle Turf"
    .AutoFilter Field:=7, Criteria1:="Hunter Chase"
    .AutoFilter Field:=10, Criteria1:="1"
    .AutoFilter Field:=10, Criteria1:="2"
    .AutoFilter Field:=10, Criteria1:="3"
    .AutoFilter Field:=10, Criteria1:="4"
    .AutoFilter Field:=38, Criteria1:="100"
    .AutoFilter Field:=43, Criteria1:="0"
    .AutoFilter Field:=43, Criteria1:="1"
    .AutoFilter Field:=71, Criteria1:="="
    .AutoFilter Field:=41, Criteria1:=">=30"
    .AutoFilter Field:=41, Criteria1:="0"
    .AutoFilter Field:=41, Criteria1:="="
    .AutoFilter Field:=5, Criteria1:=">=80"
    .AutoFilter Field:=50, Criteria1:="1"
    .AutoFilter Field:=50, Criteria1:="2"
    .AutoFilter Field:=50, Criteria1:="3"
        End With
End Sub

Do you spot anything in there which would cause it not to work correctly? I have counted the the correct column numbers, but maybe if I reference the column titles instead. They are all in row 1.

cheers

 

[James006]

Hello,

Indeed, you can use Arrays for many criteria ...

see explanation :

https://www.thesmallman.com/autofilter-on-multiple-conditions

Hope this will help

 

[honkin]

Hello,

Indeed, you can use Arrays for many criteria ...

see explanation :

https://www.thesmallman.com/autofilter-on-multiple-conditions

Hope this will help

cheers James006

I have been able to get the macros working using arrays, all bar one work fine, but one fails with a 1004 message on this line of code:-

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

What would stop the macro working in this line? is it not permitted to user >=, as other than that, I can' see what is different?

cheers

 

[James006]

Hello,

Have you tested : Operator:=xlOr

Hope this will help

 

[honkin]

cheers James006

It certainly takes away the error message, but the macro gives a different result than doing the same filters manually. It's odd as it is the only macro not working out of about 6 that now use array for multiple criteria in a single column.

I thought an Operator:=xlFilterValues with more than 2 value was automatically xlOr anyway. It certainly removes the 1004 error, but just doesn't work correctly

So somehow just this macro out of a handful is being stopped from working correctly, but still not sure why. Incidentally, if it helps, this is all of the code for that particular one

Sub Debut()
'
' DebutDestroyer Macro
' This macro will filter for VDW Win %, Class, VDW Form, RP Rating, J&T, Race Type and Contender
'
Dim last As Long
last = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    With ActiveSheet.Range("A1:BU" & last)
    .AutoFilter Field:=7, Criteria1:=Array("Flat Turf", "NH Flat", "Hurdle Turf", "Hunter Chase"), Operator:=xlFilterValues
    .AutoFilter Field:=10, Criteria1:=Array("1", "2", "3", "4"), Operator:=xlFilterValues
    .AutoFilter Field:=38, Criteria1:="100"
    .AutoFilter Field:=43, Criteria1:="=0", Operator:=xlOr, Criteria2:="=1"
    .AutoFilter Field:=71, Criteria1:="="
    .AutoFilter Field:=41, Criteria1:=Array(">=30", "0", "="), Operator:=xlOr
    .AutoFilter Field:=5, Criteria1:=">=80"
    .AutoFilter Field:=50, Criteria1:=Array("1", "2", "3"), Operator:=xlFilterValues
    End With
End Sub

Thanks so much for your patience