Delete Method of Range class Failed

AWood4081

New Member
Joined
Oct 21, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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.

VBA 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.
 
Last edited by a moderator:

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
57,028
Office Version
  1. 365
Platform
  1. Windows
It should be
Rich (BB code):
ActiveSheet.Range("A2:A" & LR).Offset(1).
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
57,028
Office Version
  1. 365
Platform
  1. Windows
Also it's very rare that you need to select anything, so your code could be written like
VBA Code:
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
 
Solution

AWood4081

New Member
Joined
Oct 21, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hello Fluff,

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

VBA 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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
57,028
Office Version
  1. 365
Platform
  1. Windows
Glad you sorted it thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,422
Messages
5,642,021
Members
417,250
Latest member
spr1nger

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top