Delete of rows is failing

Good morning Experts,

I am on Phase 7 of a project and have run into a snag. The code is supposed to be deleting rows that contain  the values "0" in column 3 and "#N/A" in column 12 in worksheet EQ Totals. I am being thrown an error code of "Runtime error 1004: Autofilter method of range class failed.

I've attached a copy of the test workbook for reference with the macros enabloed.

Any help would be appreciated
Who is Participating?

Try this one.

Dim LastRow     As Long

With Sheets("EQ Totals")
    .Range("A1").Value = "a"
    .AutoFilterMode = False
    LastRow = .Range("a" & .Rows.Count).End(xlUp).Row
    With .Range("a1:l" & LastRow)
        .AutoFilter Field:=3, Criteria1:="0", Operator:=xlOr, Criteria2:=""
        .AutoFilter Field:=12, Criteria1:="0", Operator:=xlOr, Criteria2:=""
        On Error Resume Next
        Set rData = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
    End With
    On Error GoTo 0
    If Not rData Is Nothing Then
    End If
    .AutoFilterMode = False
End With

Open in new window

The problem is that you are setting the autofilter just on range("A1").
When you do this, Excel guesses at the range to use. It will try to use only what is connected to A1. In your case, this makes it miss a lot.
If you use this instead
    .UsedRange.AutoFilter Field:=3, Criteria1:="0", Operator:=xlOr, Criteria2:=""
    .UsedRange.AutoFilter Field:=12, Criteria1:="0", Operator:=xlOr, Criteria2:=""

Then it will use all the cells that have data in them for the filter instead of just the ones connected to A1
NorieVBA ExpertCommented:
Just curious, but shouldn't you be filtering the 12th for #N/A, not 0.
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.


I didn't change OP's code. Just added the range for autofilter.

NorieVBA ExpertCommented:

Sorry, the comment was for the OP, I know the code in the file filters for 0.

There is quite a difference when filtering by #N/A.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.