Array not working

[honkin]

I have a VBA macro which autofilters, then hides certain columns and copies the result to another sheet, but I am having some issues with 1 of the lines of code

Sub SB_20_Win()
'
' CD Winner, Forecast Rank, Class, Non-Handicaps, PR
'
    Dim ws As Worksheet, lc As Long, lr As Long

    Set ws = ActiveSheet
    'range from A1 to last column header and last row
    lc = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    lr = ws.Cells.Find("*", after:=ws.Range("A1"), LookAt:=xlPart, _
                        SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    With Application
             .Calculation = xlCalculationManual
            .ScreenUpdating = False
            .DisplayStatusBar = False
            .EnableEvents = False
    End With
    With ws.Range("A1", ws.Cells(lr, lc))
            .HorizontalAlignment = xlCenter
            .AutoFilter Field:=3, Criteria1:="<>*Handicap*"
            .AutoFilter Field:=7, Criteria1:=Array("<>NH Flat", "<>Flat AW", "<>Flat Turf")
            .AutoFilter Field:=62, Criteria1:="=4", Operator:=xlOr, Criteria2:="=2"
            .AutoFilter Field:=65, Criteria1:="=Closer", Operator:=xlOr, Criteria2:="=Mid-Pack"
            .AutoFilter Field:=10, Criteria1:="<>4", Operator:=xlOr, Criteria2:="<>5"
            .AutoFilter Field:=73, Criteria1:="=2"
            .AutoFilter Field:=24, Criteria1:="~*~*"
         If .Rows.Count - 1 > 0 Then
        On Error Resume Next
            .Columns("C:C").EntireColumn.Hidden = True
            .Columns("G:G").EntireColumn.Hidden = True
            .Columns("I:I").EntireColumn.Hidden = True
            .Columns("K:L").EntireColumn.Hidden = True
            .Columns("N:W").EntireColumn.Hidden = True
            .Columns("Y:Z").EntireColumn.Hidden = True
            .Columns("AB:AK").EntireColumn.Hidden = True
            .Columns("AO:AO").EntireColumn.Hidden = True
            .Columns("AQ:BK").EntireColumn.Hidden = True
            .Columns("BO:BS").EntireColumn.Hidden = True
            .Columns("BV:BY").EntireColumn.Hidden = True
            .Columns("CA:CA").EntireColumn.Hidden = True
            .Columns("CC:CG").EntireColumn.Hidden = True
            .Columns("CI:CK").EntireColumn.Hidden = True
        If .Columns(1).SpecialCells(xlVisible).Count > 1 Then
        .Offset(1, 0).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Copy
        Else
            Exit Sub
        End If
            On Error GoTo 0
        End If
    End With
     
    Workbooks("New Results File Active Football Advisor.xlsm").Sheets("Safe Bets") _
          .Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
   
    Application.CutCopyMode = False
    With Application
            .Calculation = xlCalculationAutomatic
            .ScreenUpdating = True
            .DisplayStatusBar = True
            .EnableEvents = True
    End With
    Call Reset_Sheet
End Sub

The array line saying does not contain NH Flat, Flat AW and Flat Turf doesn't seem to work correctly, with a runner in an NH Flat race being selected.

What have I done wrong in that line and as a general critique, is the code efficient enough for large files, or is there a more streamlined way to achieve what I am after?

Thanks in advance

 

[RoryA]

You can't use an array with <> criteria, I'm afraid. Also, your criteria on field 10 will match anything since 4 is not 5.

 

[honkin]

You can't use an array with <> criteria, I'm afraid. Also, your criteria on field 10 will match anything since 4 is not 5.

Cheers Rory. Thanks for your reply.

I changed field 10 to be xlAnd instead of xlOr and that seems to work fine, but cannot get the track names to filter correctly. When I exclude that line, the rest of the macro works well.

Sub SB_20_Win()
'
' CD Winner, Forecast Rank, Class, Non-Handicaps, PR
'
    Dim ws As Worksheet, lc As Long, lr As Long

    Set ws = ActiveSheet
    'range from A1 to last column header and last row
    lc = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    lr = ws.Cells.Find("*", after:=ws.Range("A1"), LookAt:=xlPart, _
                        SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    With Application
             .Calculation = xlCalculationManual
            .ScreenUpdating = False
            .DisplayStatusBar = False
            .EnableEvents = False
    End With
    With ws.Range("A1", ws.Cells(lr, lc))
            .HorizontalAlignment = xlCenter
            .AutoFilter Field:=3, Criteria1:="<>*Handicap*"
            .AutoFilter Field:=7, Criteria1:=Array("Chase Turf", "Hurdle Turf", "Hunter Chase")
            .AutoFilter Field:=62, Criteria1:=Array("0", "2"), Operator:=xlFilterValues
            .AutoFilter Field:=65, Criteria1:="=Closer", Operator:=xlOr, Criteria2:="=Mid-Pack"
            .AutoFilter Field:=10, Criteria1:="<>4", Operator:=xlAnd, Criteria2:="<>5"
            .AutoFilter Field:=73, Criteria1:="=2"
            .AutoFilter Field:=24, Criteria1:="~*~*"
         If .Rows.Count - 1 > 0 Then
        On Error Resume Next
            .Columns("C:C").EntireColumn.Hidden = True
            .Columns("G:G").EntireColumn.Hidden = True
            .Columns("I:I").EntireColumn.Hidden = True
            .Columns("K:L").EntireColumn.Hidden = True
            .Columns("N:W").EntireColumn.Hidden = True
            .Columns("Y:Z").EntireColumn.Hidden = True
            .Columns("AB:AK").EntireColumn.Hidden = True
            .Columns("AO:AO").EntireColumn.Hidden = True
            .Columns("AQ:BI").EntireColumn.Hidden = True
            .Columns("BK:BL").EntireColumn.Hidden = True
            .Columns("BO:BS").EntireColumn.Hidden = True
            .Columns("BV:BY").EntireColumn.Hidden = True
            .Columns("CA:CA").EntireColumn.Hidden = True
            .Columns("CC:CG").EntireColumn.Hidden = True
            .Columns("CI:CK").EntireColumn.Hidden = True
        If .Columns(1).SpecialCells(xlVisible).Count > 1 Then
        .Offset(1, 0).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Copy
        Else
            Exit Sub
        End If
            On Error GoTo 0
        End If
    End With
     
    Workbooks("New Results File Active Football Advisor.xlsm").Sheets("Safe Bets") _
          .Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
   
    Application.CutCopyMode = False
    With Application
            .Calculation = xlCalculationAutomatic
            .ScreenUpdating = True
            .DisplayStatusBar = True
            .EnableEvents = True
    End With
    Call Reset_Sheet
End Sub

What I need is for field 7 to filter for any of those 3 track names, but when I run the macro, nothing is selected, yet when I filter manually, there are 2 selections.

Any thoughts on why that line doesn't work?

cheers

 

[RoryA]

You need to specify the Operator:=xlfiltervalues argument for that line

 

[honkin]

You need to specify the Operator:=xlfiltervalues argument for that line

ah, light bulb moment. Thanks for spotting a basic error. Yep, it works fine now

cheers