Excel autofilter errors

Question

I am trying to autofilter on any cell with #DIV/0 using the code below however it keeps returning "Some Other Error" even though there are the above errors in column A.

Sub asdf2()
Dim R As Range
Set R = Range("A:A")

If IsError(R.Value) = True Then
    If R.Value = CVErr(xlErrDiv0) Then
        With R
            .AutoFilter field:=1, Criteria1:=R.Value
        End With
    End If

    Else
        Debug.Print "Some other error"
End If

End Sub

Answer 1

As R is a multi cell range spanning a whole column, there is no .Value property.

Debug until you executed the Set R = ... statement and then examine the properties of R in the Locals window.

Secondly - interpreting your Else / Debug.Print branch, you are looking only for cells containing any error. Your Else branch will also be executed for cells not containing any error. So your Else branch should be attached to the inner If

Lastly, you cannot provide an error value as an argument for .Autofilter ... use a string Criteria1:="#DIV/0!" instead.

Mocking this all up ...

Sub asdf2()
Dim R As Range, C As Range

    Set R = Range("A:A")

    For Each C In R.Cells
        Debug.Print C.Value
        If IsError(C.Value) = True Then
            If C.Value = CVErr(xlErrDiv0) Then
                With R
                    .AutoFilter field:=1, Criteria1:="#DIV/0!"
                End With
            Else
                Debug.Print "Other Error"
            End If
            Exit For
        End If
    Next C
End Sub

So here

1. on detecting a #DIV/0! error an autofilter is set
2. on detecting any other error a debug.print is issued
3. otherwise nothing happens

in case of 1,2 the loop is terminated immediately - no further checking