CODEWITHSUNDEEP
vbaautofilter
Nagarjun
Nagarjun

Reputation: 11

macros autofilter not working

The following program is showing error message "'AutoFilter' method of Range class failed", and I am not able to find the solution for this:

Sub filterforaging()

' filterforaging Macro
'

'
    Columns("i:i").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Rows("1:1").Select
    Selection.AutoFilter
    Range("I1").Select
    ActiveSheet.Range("$A$1:$I$10000").AutoFilter Field:=1, Criteria1:=Array( _
        ">10 days", "5-8days", "8-10days"), Operator:=xlFilterValues
    Application.CutCopyMode = False
End Sub

Upvotes: 0

Views: 9696

Answers (1)

user3598756
user3598756

Reputation: 29421

First off ">10 days" must be "*>10 days", to (I guess) avoid the ">" part to lead to a comparison criteria

But then again there remains a weird behavior related to "*>10 days" filter:

  • if you filter by it alone

    .AutoFilter field:=1, Criteria1:="*>10 days"

    it would correctly filter the wanted rows

  • if you filter by it and another value only

    .AutoFilter field:=1, Criteria1:=Array("*>10 days", "8-10days"), Operator:=xlFilterValues

    it would still work correctly

  • but if you filter by it and another two values 

    .AutoFilter field:=1, Criteria1:=Array("*>10 days", "5-8days", "8-10days"), Operator:=xlFilterValues

    then it would only filter the other two values

As to now I could only get to the following workaround:

Dim rng1 As Range, rng2 As Range

With ActiveSheet.Range("$A$1:$I$10000")
    .Rows(1).AutoFilter
    .AutoFilter field:=1, Criteria1:="*>10 days"
    If Application.WorksheetFunction.Subtotal(103, .Resize(, 1)) - 1 > 0 Then Set rng1 = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)

    .AutoFilter field:=1, Criteria1:=Array("5-8days", "8-10days"), Operator:=xlFilterValues
    If Application.WorksheetFunction.Subtotal(103, .Resize(, 1)) - 1 > 0 Then Set rng2 = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)

    .AutoFilter
    .Columns(1).Resize(.Rows.Count - 1).Offset(1).EntireRow.Hidden = True
    If Not rng1 Is Nothing Then rng1.EntireRow.Hidden = False
    If Not rng2 Is Nothing Then rng2.EntireRow.Hidden = False
End With

Upvotes: 2

Related Questions