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
test.xlsm
RedstoneITAsked:
Who is Participating?
 
krishnakrkcCommented:
Hi,

Try this one.

Kris
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
        rData.EntireRow.Delete
    End If
    .AutoFilterMode = False
    .Range("A1").Clear
End With

Open in new window

0
 
TommySzalapskiCommented:
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
0
 
NorieVBA ExpertCommented:
Just curious, but shouldn't you be filtering the 12th for #N/A, not 0.
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.

 
krishnakrkcCommented:

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

0
 
NorieVBA ExpertCommented:
Krishnakrkc

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.
0
 
krishnakrkcCommented:

:)
0
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.