WWII_Buff

Board Regular
Joined
Nov 13, 2017
Messages
88
Hey gang! Can someone please help me understand why this isn't working: the error I am getting is "Run-time error '1004: Autofilter method of Range class failed." Thank you!

Code:
Sub Criteria_Filter()



With Sheets("MASTER")
.Range("4:4").AutoFilter Field:=2, Criteria1:=Array( _
"<>TOTAL*", "<>0", "99999"), Operator:=xlFilterValues




.Range("4:4").AutoFilter Field:=7, Criteria1:=Array( _
"<>", "<>G&A"), Operator:=xlFilterValues


End With


End Sub
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Chameleon64

New Member
Joined
May 10, 2018
Messages
40
Interesting problem! I found a workaround here with an explanation in #14 : "The .autofilter only has 2 criteria you can use and using an array for the criteria doesn’t allow you to use the “<>Name1” format."
 

WWII_Buff

Board Regular
Joined
Nov 13, 2017
Messages
88
Thank you for that find! Thanks for explaining why the code was erroring out. It was confusing as heck - 1st with the 3rd criteria without the array, then with the array - which I figured would solve the issue. I may need help tailoring it for what I need it to do. Grrrr!
 

WWII_Buff

Board Regular
Joined
Nov 13, 2017
Messages
88
Interesting problem! I found a workaround here with an explanation in #14 : "The .autofilter only has 2 criteria you can use and using an array for the criteria doesn’t allow you to use the “<>Name1” format."
Here is my edit: However, it gave me an error code: Run-time error 13, Type mismatch here
Code:
If UBound(Filter(secondArray, c)) = -1 Then
Code:
   Sub FilterCriteria()

    'apply a filter, row 1 contains my titles
    Range("4:4").Select
    Selection.AutoFilter
 
    Dim FilterCriteria() As String
    Dim count As Long, secondArray As Variant
    Dim L As Long, c As String, k As String, rowNumb As Long
   
    secArray = Array("99999", "TOTAL*", "0")
   
    c = 0
    k = 0
    count = 0
    rowNumb = ActiveSheet.Range(ActiveSheet.Range("B5"), ActiveSheet.Range("B4").End(xlDown)).Rows.count
   
    For L = 1 To rowNumb
        c = ActiveSheet.Range("B4").Offset(L)
        If c <> k Then
            'check the current activity type against the array of types we don’t want. If it isn’t in the array we add it to an array that will be used as the filter criteria
            If UBound(Filter(secondArray, c)) = -1 Then
                ReDim Preserve FilterCriteria(0 To count)
                FilterCriteria(count) = c
                count = count + 1
            End If
 
            k = c
        End If
    Next
   
    With ActiveSheet
        .Range(.Range("A1"), .Range("A1").End(xlDown).Offset(0, 11)).AutoFilter Field:=2, Criteria1:=FilterCriteria, Operator:=xlFilterValues
    End With
    
   End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,095,424
Messages
5,444,384
Members
405,279
Latest member
yousef alotra

This Week's Hot Topics

Top