0

I am facing the good old runtime error 1004 with the following code

Worksheets("abc").Range(Cells(4, 9), Cells(15, 9)).ClearContents

I have tried Worksheets("abc").Range("I4:I15").ClearContents and it works.

The reason why I need the Range(Cells(...)) format is because I actually write

Worksheets("abc").Range(Cells(4, 9), Cells(Max, 9)).ClearContents

So I want to control the range via a variable. For me the error doesn't make any sense, but well with Excel you never know ;)

Moritz
  • 1
  • Is the sheet protected? – Nathan_Sav Jul 14 '21 at 10:51
  • 4
    `Cells(4, 9)` is not qualified so will apply to the active sheet, not necessarily "abc". – SJR Jul 14 '21 at 11:34
  • @SJR I've had a lot of trouble with that specific interaction. VBA tries to build a range in one sheet but using cell objects from a different sheet, which gives `Runtime error '1004': Method 'Range' of object '_Worksheet' failed`. This is very likely the issue. – Toddleson Jul 14 '21 at 12:47
  • 1
    `With Worksheets("abc")`, `.Range(.Cells(4, 9), .Cells(Max, 9)).ClearContents`, `End With`. Note the periods `.` in front of `Range` and `Cells`. – BigBen Jul 14 '21 at 13:15

0 Answers0