Error: 1004 - Delete Method of Range Class failed

Question

I have a large Excel sheet which imports data from other sheets.

After the import is done, I use a function in column V which displays whether the line is to be deleted or not. If a line is to be delete the formula will display "Delete".

I run the same code for a few tabs: Tab AA, Tab BB, and so on.

The code shown here works for the first tab, but does displays error on the other tabs. I tried a few variation but could not get it to work.

p is already defined and indicated the number of lines to audit. Other sheets used other variable than p to track the number of lines.

Alternatively I also tried using a different variable such as ji, but then ji just takes the value of p

    Sheets("BB").Select
    
    For J = 3 To p
       
       Sheets("BB").Range("V" & J).Select
       If Sheets("BB").Range("V" & J) = "Delete" Then
            ActiveCell.EntireRow.Delete
            'Sheets("BB").Rows(J).Delete  'one of the many variation I tried
            J = J - 1
        End If
    
    Next J

Another variation of the code is as follow, with w the number of line to audit

    Sheets("CC").Select
    For J = 3 To w
    
        If Sheets("CC").Range("V" & J) = "Delete" Then
            Rows(J).Delete
            J = J - 1
        End If
    
    Next J

The solution should be easily replicable as I will need to use the same code to clean 6 more tabs.

Thanks in advance for the help

You're taking the wrong approach to deleting. [Use `Union`](https://stackoverflow.com/a/59975507/9245853) and only delete *after* the loop has finished. — BigBen, Apr 07 '21 at 16:33
Thank you for the advise, I gave it a try but I'm afraid I'm not familiar with this command. how would you go about it ? — user2453446, Apr 07 '21 at 16:51

score 0 · Answer 1 · answered Apr 07 '21 at 16:50

Delete Rows Using Union

Either use Application.Union (recommended) or a backward loop (For J = p To 3 Step - 1) to not skip rows.

Dim drg As Range
For j = 3 To p
    With Worksheets("BB").Range("V" & j)
        If .Value = "Delete" Then
            If drg Is Nothing Then
                Set drg = .EntireRow
            Else
                Set drg = Union(drg, .EntireRow)
            End If
        End If
    End With
Next j
If Not drg Is Nothing Then
    drg.Delete
End If

Error: 1004 - Delete Method of Range Class failed

1 Answers1

Delete Rows Using Union