AutoFilter Method Of Range Class Failed

Hi all,

Using Excel 2010.

I need 2 filter a Range to remove 2 values in the Range: "-" (Accounting zero format) and 00 (left justified as text)

I receive an AutoFilter Method Of Range Class Failed error message here:

        rngFilter.AutoFilter _
                    Field:=2, _
                    Criteria1:=Array("<>-", "<>00"), _
                    Operator:=xlFilterValues

The Range definitely exists. Any other thoughts?

thx
w

Maybe I am missing it, but I don't see where you specify a range

Thanks gmr4evr1,

I just did not include it in the snippet as the Range does exist and the problem is with the AutoFilter.

With ws
Set rngFilter = .Range(.Cells(6,1),.Cells(6,lngRows))
End With

        rngFilter.AutoFilter _
                    Field:=2, _
                    Criteria1:=Array("<>-", "<>00"), _
                    Operator:=xlFilterValues

thx
w

Why is your column variable called lngRows?

Romperstomper,

My typo,

I typed in the code rather than copy paste.
Should be

With ws
Set rngFilter = .Range(.Cells(6,1),.Cells(lngRows,2))
End With

        rngFilter.AutoFilter _
                    Field:=2, _
                    Criteria1:=Array("<>-", "<>00"), _
                    Operator:=xlFilterValues

I printed the address of the range, came back as $A$6:$B$20

thx
w

Originally Posted by goss

I typed in the code rather than copy paste.

Why??

Anyway, you don't really want an array here:

        rngfilter.AutoFilter _
                    Field:=2, _
                    Criteria1:="<>-", Operator:=xlAnd, Criteria2:="<>00"

goss, Try this one:?

rngFilter.AutoFilter _
                    Field:=2, _
                    Criteria1:="<>-", Criteria2:="<>00", _
                    Operator:=xlFilterValues

Deleted content as I posted twice

Thanks all,

None of those option worked. Appears nothing is filtered out.

I recorded this macro:

Sub Macro3()
'
' Macro3 Macro
'

'
    Range("A6:B658").Select
    Range("B658").Activate
    Selection.AutoFilter
    ActiveSheet.Range("$A$6:$B$659").AutoFilter Field:=2, Criteria1:=Array( _
        "-0.01", "0.23", "-0.23", "2.27374E-12"), Operator:=xlFilterValues
End Sub

thx
w

The broblem being that I never know what the values need to be included, only what should be excluded.

odd goss, they filter out on my end when one cell has a - or 00 (or some of each in the list), in column 2. Can you by chance post your book so we can have a look?

Edit:

Are you trying to exlude a cell with ONLY 00 or - in the cell, or are you trying to exclude them as part of the data?

Thanks Arkadi,

Are you trying to exlude a cell with ONLY 00 or - in the cell

Yes. Sample attached. Cutdown for size. Obfuscated for confidentiality.

thx
w

Try this version:

Sub Macro1()
    Dim LastRow                     As Long
    Dim n                           As Long
    Dim vKeys

    LastRow = Cells(Rows.Count, "A").End(xlUp).Row

    With CreateObject("Scripting.Dictionary")
        For n = 7 To LastRow
            Debug.Print Cells(n, "B").Value
            Select Case Cells(n, "B").Text
                Case "00", "-"
                    ' ignore
                Case Else
                    .Item(Cells(n, "B").Text) = Empty
            End Select
        Next n
        vKeys = .keys
    End With
    ActiveSheet.Range("A6:B" & LastRow).AutoFilter Field:=2, Criteria1:=vKeys, Operator:=xlFilterValues
End Sub

Thanks Romper,

I'm not strong enough with Dictionaries to feel comfortable.
I ended up replacing zero's and non-numeric with "Blank" and filtering "<>Blank"

Not great and I think using 2 criteria or criteria array would be faster, but this does work.

thx
w

    'Determine rows on retrieve sheet
        lngRowsRetrieve = GetLast(ws:=wsRtrv, _
                                  RC:="r") - 1
                                  
    'Create range for replacements
        With wsRtrv
            Set rngReplace = .range(.Cells(7, 2), .Cells(lngRowsRetrieve, 2))
        End With

    'Replace zero's and alpha's
        For Each C In rngReplace
            If C.Value = 0 Or Not IsNumeric(C.Value) Then
                C.Value = "Blank"
            End If
        Next C
        
    'Create range for filter
        With wsRtrv
            Set rngFilter = .range(.Cells(6, 1), .Cells(lngRowsRetrieve, 2))
        End With
        
    'Filter out "Blank"
        rngFilter.AutoFilter _
                    Field:=2, _
                    Criteria1:="<>Blank", _
                    VisibleDropDown:=True