0

The below code produces the error "Run-time error '1004': Method 'Range' of object"_Worksheet' failed" if the referenced sheet is not active. However, If I have the sheet active and run the code everything works. I believe the worksheet in question is properly set as an object for the code to reference and I have tried several different setups to target it. 

Private Sub clearImport()
Dim Ws As Worksheet


Set Ws = ThisWorkbook.Sheets("Returned")
k = Ws.Range("P65536").End(xlUp).Offset(1).Row
Ws.Range(Cells(2, 1), Cells(k, 23)).Delete

End Sub
cybernetic.nomad
  • 6,100
  • 3
  • 18
  • 31
jwb2j
  • 1
  • 1
  • 4
    `Cells` is referring to the `ActiveSheet` while `Range` is looking at the _Returned_ sheet. It should be `Ws.Range(WS.Cells(2, 1), WS.Cells(k, 23)).Delete` – Darren Bartrup-Cook Sep 04 '18 at 15:24
  • Perfect, thank you. I thought the reference carried through from the Ws object. – jwb2j Sep 04 '18 at 15:27
  • You should also add `Dim k As Long` and go into `Tools ~ Options` and tick `Require Variable Declaration` on the `Editor` tab. – Darren Bartrup-Cook Sep 04 '18 at 15:27
  • Afraid not. You could use [With...End With](https://learn.microsoft.com/en-us/dotnet/visual-basic/language-reference/statements/with-end-with-statement) to shorten it a bit though. – Darren Bartrup-Cook Sep 04 '18 at 15:28
  • 2
    Welcome to the club - https://stackoverflow.com/search?q=%22Range%28Cells%28%22+1004 This is really the most common error in the VBA tag, without a doubt. I guess that everyone has hit it once and wondered a bit what was wrong. – Vityata Sep 04 '18 at 15:30
  • 1
    Might be worth using `ClearContents` rather than `Delete` if you're getting `#REF!` errors after deleting. – Darren Bartrup-Cook Sep 04 '18 at 15:32
  • [Interesting read.](https://stackoverflow.com/questions/36368220/is-the-in-range-necessary-when-defined-by-cells) –  Sep 04 '18 at 15:59

0 Answers0