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
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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."
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,188
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top