Delete Method of Range class Failed

[AWood4081]

Hello,

I am having an issue with the following code. As i am trying to copy filtered data from one page to another and then go back to the original data page and delete the transferred data. I keep getting hung on this line with the Error 1004 Delete Method of Range Class Failed.

ActiveSheet.Range("A1" & LR).Offset(1).SpecialCells _
(xlCellTypeVisible).EntireRow.Delete

For more context here is the whole code.

  Sub Trasfer2()
                  Application.ScreenUpdating = False
                  Dim LR As Long
    
                  ActiveSheet.Range("K1").AutoFilter Field:=11, Criteria1:="<>"
                  LR = Range("A" & Rows.Count).End(xlUp).Row
                  Range("A2:K" & LR).SpecialCells(xlCellTypeVisible).Select
                  Selection.Copy
                  Sheets("Closed").Select
                  ActiveSheet.Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial

                  Sheets("Data").Select
                  ActiveSheet.Range("A1" & LR).Offset(1).SpecialCells _
                  (xlCellTypeVisible).EntireRow.Delete
    
                  ActiveSheet.Range("A1").Select
                  Sheet2.ShowAllData

Thank you in advance for any advice or help.

 

[Fluff]

It should be

ActiveSheet.Range("A2:A" & LR).Offset(1).

 

[Fluff]

Also it's very rare that you need to select anything, so your code could be written like

Sub AWood()
   With Sheets("Data")
      .Range("A1:K1").AutoFilter 11, "<>"
      .AutoFilter.Range.Offset(1).EntireRow.Copy Sheets("Closed").Range("A" & Rows.Count).End(xlUp).Offset(1)
      .AutoFilter.Range.Offset(1).EntireRow.Delete
      .AutoFilterMode = False
   End With
End Sub

 

[AWood4081]

Hello Fluff,

Thank you for your speedy response unfortunately I still get the same error. I ended up using the following code:

Sheets("Data").Select
    Rows("2:" & LR).Delete Shift:=xlUp

That seems to have fixed my issue.

Again I really appreciate your responses! I will def use your advice in regards to the not needing to select the sheets!

 

[Fluff]

Glad you sorted it thanks for the feedback.