2 ways for "ClearContents" on VBA Excel, but 1 work fine. Why? [duplicate]

Question

Good evening friends:

I have in mind 2 ways for clearing a content in a defined range of cells of a VBA project (in MS Excel):

1. Worksheets("SheetName").Range("A1:B10").ClearContents
2. Worksheets("SheetName").Range(Cells(1, 1), Cells(10, 2)).ClearContents

The problem is that the second way show me an error '1004' when I'm not watching the current Worksheet "SheetName" (in other words, when I haven't "SheetName" as ActiveSheet).

The first way work flawlessly in any situation.

Why does this happen? How can I use the "Second way" without this bug?

Answer 1

It is because you haven't qualified Cells(1, 1) with a worksheet object, and the same holds true for Cells(10, 2). For the code to work, it should look something like this:

Dim ws As Worksheet

Set ws = Sheets("SheetName")
Range(ws.Cells(1, 1), ws.Cells(10, 2)).ClearContents

Alternately:

With Sheets("SheetName")
    Range(.Cells(1, 1), .Cells(10, 2)).ClearContents
End With

EDIT: The Range object will inherit the worksheet from the Cells objects when the code is run from a standard module or userform. If you are running the code from a worksheet code module, you will need to qualify Range also, like so:

ws.Range(ws.Cells(1, 1), ws.Cells(10, 2)).ClearContents

or

With Sheets("SheetName")
    .Range(.Cells(1, 1), .Cells(10, 2)).ClearContents
End With