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

12
votes

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?

3
excelvba
Not for points but was my answer that difficult to understand? – Siddharth Rout
@pnuts: No I don't think :@tigeravatar is desperate :) It was just a difference of few minutes :) – Siddharth Rout
Default scopes for range-related references: stackoverflow.com/questions/28439376/… – Tim Williams

3 Answers

15
votes

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
11
votes

That is because you are not fully qualifying your cells object. Try this

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

Notice the DOT before Cells?

0
votes

For numerical addressing of cells try to enable S1O1 checkbox in MS Excel settings. It is the second tab from top (i.e. Formulas), somewhere mid-page in my Hungarian version.

If enabled, it handles VBA addressing in both styles, i.e. Range("A1:B10") and Range(Cells(1, 1), Cells(10, 2)). I assume it handles Range("A1:B10") style only, if not enabled.

Good luck!

(Note, that Range("A1:B10") represents a 2x10 square, while Range(Cells(1, 1), Cells(10, 2)) represents 10x2. Using column numbers instead of letters will not affect the order of addresing.)