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?

Community
  • 1
  • 1
Asrhael
  • 149
  • 1
  • 1
  • 7

3 Answers3

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
tigeravatar
  • 26,199
  • 5
  • 30
  • 38
  • You might want to add `ws.` before the `Range`? `ws.Range(ws.Cells(1, 1), ws.Cells(10, 2)).ClearContents` – Siddharth Rout Sep 23 '13 at 15:22
  • Similarly adding a DOT for the second code `.Range(.Cells(1, 1), .Cells(10, 2)).ClearContents` – Siddharth Rout Sep 23 '13 at 15:24
  • That's not necessary. Range will inherit the worksheet of the Cells objects if Range hasn't been provided with a sheet – tigeravatar Sep 23 '13 at 15:24
  • `That's not necessary. Range will inherit the worksheet of the Cells objects if Range hasn't been provided with a sheet – tigeravatar 45 secs ago` Are you sure? ;) Try running the code from a different sheet... – Siddharth Rout Sep 23 '13 at 15:26
  • I did, and it worked just fine for me. :) – tigeravatar Sep 23 '13 at 15:27
  • Try this [code](http://wikisend.com/download/571942/1.png) in the sheet2 code area ;) – Siddharth Rout Sep 23 '13 at 15:28
  • But, for example, if I want to use a value stored in "A1" (and in the sheet "SheetName"), I can use correctly Worksheets("SheetName").Cells(1, 1) without problem. Why in this case I don't need to "qualify" the Cells(1,1)? – Asrhael Sep 23 '13 at 15:30
  • Now I understand completely, really really thanks friends. Hope you have a nice day! – Asrhael Sep 23 '13 at 15:38
  • @SiddharthRout When I run that code, it completes successfully, and the appropriate cells on sheet1 are colored red, even when sheet2 or sheet3 is the activesheet. I'm not sure what you're trying to get at. The proposed solution works successfully for me in all cases. I have used this method many times before. Are you getting an error? – tigeravatar Sep 23 '13 at 15:41
  • It's difficult for me to believe :) Yes, as expected, I am getting an error. Do you have TeamViewer? – Siddharth Rout Sep 23 '13 at 15:45
  • @SiddharthRout I have [created a chat](http://chat.stackoverflow.com/rooms/37895/chat-between-tigeravatar-and-siddarthrout) where we can continue this discussion :) – tigeravatar Sep 23 '13 at 15:51
  • 1
    + 1 For incorporating the suggestion :) – Siddharth Rout Sep 23 '13 at 16:11
  • 1
    @SiddharthRout Interesting, so what were the results of your discussion? I did some testing of my own (with xl2010), and, much to my surprise, I had the same results as tigeravatar. For myself however, I feel more comfortable to always qualify the Range object, whether it's necessary or not. – DaveU Jan 20 '17 at 13:31
  • 1
    @DaveU The discussion was about why and how the error occurs. The results of the discussion are detailed in the edit of the answer: "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" – tigeravatar Jan 20 '17 at 16:30
  • @tigeravatar Good to know. However, I am somewhat surprised that I haven't come across a mention of this before. Thanks. – DaveU Jan 21 '17 at 05:35
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?

Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
0

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.)

certifiedtranslator.hu
  • 1
  • 2