Delete of rows is failing

Posted on 2011-10-15
Medium Priority
Last Modified: 2012-08-13
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
Question by:RedstoneIT
  • 3
  • 2
LVL 37

Expert Comment

ID: 36973365
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
LVL 18

Accepted Solution

krishnakrkc earned 2000 total points
ID: 36973367

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

LVL 35

Expert Comment

ID: 36973471
Just curious, but shouldn't you be filtering the 12th for #N/A, not 0.
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 18

Expert Comment

ID: 36973556

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

LVL 35

Expert Comment

ID: 36973567

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.
LVL 18

Expert Comment

ID: 36973588


Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question