Deleting a selected row

Question

I have the below code which effectively deletes three lines within a designated worksheet (Finding ranges etc). This code I have used for atleast a year without any issues. However I have recently transferred this code to a new work book and set up a worksheet which is exactly the same as the previous workbook.

The code errors where highlighted below with the error message

Run-Time error '1004'

Delete method of range class failed

Can anyone suggest why this error would occur?

Sub DeleteRowPIC()

Application.ScreenUpdating = False
Application.Calculation = xlManual
ActiveSheet.Unprotect Password:="Projects123"
ActiveSheet.Range("Total").Select
If Selection.Row = 12 Then
Else
ActiveSheet.Range("Total").Select
Selection.Offset(-2, 0).Select
ActiveCell.EntireRow.Delete

ActiveSheet.Range("Total_1").Select
Selection.Offset(-2, 0).Select
ActiveCell.EntireRow.Delete **ERROR OCCURS HERE**

ActiveSheet.Range("Total_2").Select
Selection.Offset(-2, 0).Select
ActiveCell.EntireRow.Delete

End If
Range("K2").Select
Application.Calculation = xlAutomatic
With ActiveSheet

      .Protect Password:="Projects123", UserInterfaceOnly:=True
      .EnableOutlining = True
End With

Have you definitely got a named range "Total_1" in the new worksheet? — Clusks, Jun 28 '16 at 10:23
@Sean Bailey does your "Total_1" named range starts from row 1 or 2 ? — Shai Rado, Jun 28 '16 at 10:32
You really shouldn't use `.Select`, it's very slow and easily produces errors. — Tom K., Jun 28 '16 at 10:44
@Clusks Yes this issue occurs at the delete line. I.e. it finds the relevant row which is required to be deleted however does not delete the line due to run time error 1004. — Sean Bailey, Jun 28 '16 at 12:26
Does the range Range("Total_1").Offset(-2,0) exist after you delete the first row(s)? My inclination is that it does not, and that is why you are getting an error. You should learn to [avoid select](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) as well. It will produce less buggy code. — Kyle, Jun 28 '16 at 13:11

score 0 · Accepted Answer · answered Jun 28 '16 at 13:38

As mentioned in the comments, you should always start from the last row and work your way to the first row when deleting rows. I suspect your issue is caused by this. Without seeing your data, I would suggest working from Total_2 back to Total. Also, you should avoid using Select whenever possible. Try modifying your code to this:

Sub DeleteRowPIC()

Dim ws As Worksheet

Application.ScreenUpdating = False
Application.Calculation = xlManual

Set ws = ActiveSheet
With ws
    .Unprotect Password:="Projects123"
    If .Range("Total").Row <> 12 Then
        .Range("Total_2").Offset(-2, 0).EntireRow.Delete
        .Range("Total_1").Offset(-2, 0).EntireRow.Delete
        .Range("Total").Offset(-2, 0).EntireRow.Delete
    End If
    Application.Calculation = xlAutomatic
    .Protect Password:="Projects123", UserInterfaceOnly:=True
    .EnableOutlining = True
End With

Make sure you set Application.ScreenUpdating back to true at some point as well.

*Make sure you set Application.ScreenUpdating back to true* - this actually isn't necessary. It automatically flip back after the module is run. — Scott Holtzman, Jun 28 '16 at 13:42
Thanks, this works better than my current code and has solved the issue. :) — Sean Bailey, Jun 30 '16 at 10:29

score 0 · Answer 2 · answered May 29 '20 at 20:38

I know it's been a long time but in case anyone else stumbles across this the way I did, when I had this problem it turned out that an external data query which overlaped that row was the problem. Even deleting the results of that query didn't resolve it. However, as soon as I moved that query to a different sheet -- problem solved.

Deleting a selected row

2 Answers2

Linked