What is "Delete method of range class failed"????

[Alex O]

My code below had been working fine, but then today I started getting a "Delete method of range class failed" error. What does that mean and how is it corrected?

Sub DelDups()
 Application.ScreenUpdating = False
For i = Range("A8000").End(xlUp).Row To 2 Step -1
If Evaluate("=SumProduct(--(B4:B" & i - 1 & " =B" & i & "), --(C4:C" & i - 1 & " =C" & i & ")) ") = 1 Then
Range("B" & i).EntireRow.Delete
End If
Next i
 Application.ScreenUpdating = True
End Sub

 

[Joe4]

What is the value of i when you get that error?
If you step through your code, you should be able to see, or simply add a Message Box that returns the value of i.

 

[shg]

How about instead ...

Sub DelDups()
  Range("A:B").RemoveDuplicates Columns:=Array(1, 2)
End Sub

 

[Alex O]

I appreciate the suggestion, however, the code only deletes A:B. It's supposed to be looking for instances where columns B:C are duplicated, deleting the row(s) containing duplicates leaving only the first occurrence.

 

[Alex O]

Thanks for the input, Joe4. Would you mind explaining how I would add a Message Box that returns the value of i?

 

[Joe4]

Sub DelDups()
    Application.ScreenUpdating = False
    For i = Range("A8000").End(xlUp).Row To 2 Step -1
[COLOR=#ff0000]        MsgBox i[/COLOR]
        If Evaluate("=SumProduct(--(B4:B" & i - 1 & " =B" & i & "), --(C4:C" & i - 1 & " =C" & i & ")) ") = 1 Then
            Range("B" & i).EntireRow.Delete
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

Or better yet, add error handling that only returns it when the error occurs, like this:

Sub DelDups()

    Dim i As Long

    Application.ScreenUpdating = False
    
    On Error GoTo err_chk
    For i = Range("A8000").End(xlUp).Row To 2 Step -1
        MsgBox i
        If Evaluate("=SumProduct(--(B4:B" & i - 1 & " =B" & i & "), --(C4:C" & i - 1 & " =C" & i & ")) ") = 1 Then
            Range("B" & i).EntireRow.Delete
        End If
    Next i
    On Error GoTo 0
    
    Application.ScreenUpdating = True
    Exit Sub
    
err_chk:
    MsgBox "Value of i at time of error: " & i
    Application.ScreenUpdating = True

End Sub

So what is the value of i when the error occurs?

 

[Scott Huish]

I appreciate the suggestion, however, the code only deletes A:B. It's supposed to be looking for instances where columns B:C are duplicated, deleting the row(s) containing duplicates leaving only the first occurrence.

I think it would work if the code were modified to your requirements:

Sub Macro1()
    ActiveSheet.Cells.EntireRow.RemoveDuplicates Columns:=Array(2, 3), Header:=xlNo
End Sub

 

[Alex O]

The value of i is 987, which is also the last row in my range. If I click OK it becomes 986, then 985, and so on....

 

[Joe4]

Try using the second code I posted, that tells us the value of i when the error occurs.
Then take a look at that row, and see if there appears to be anything odd about it, like merged cells, strange/different values in this row compared to others, etc.

 

[Alex O]

That was it...hidden columns with merged cells that contained an array (=IF(NOT(ISNA(VLOOKUP(T986,T$3:T985,1,FALSE))),"",MAX(IF(T986=$T$4:$T$1782,$U$4:$U$1782,0))))
Thanks for the guidance...much appreciated!
Alex