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

12

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?

This question is tagged with excel vba

~ Asked on 2013-09-23 15:15:12

The Best Answer is


15

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

~ Answered on 2013-09-23 15:18:40


11

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?

~ Answered on 2013-09-23 15:16:44


Most Viewed Questions: