All geek questions in one place

There are 2 methods for "ClearContents" on VBA Excel, but 1 works fine. What for?

Good evening:

I have in mind 2 ways to clear content in a specific cell range of a VBA project (in MS Excel):

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

The problem is that the second method shows me the error ' 1004 ' when I do not look at the current sheetName (in other words, when I do not have a SheetName as an ActiveSheet).

The first method works flawlessly in any situation.

Why is this happening? How can I use the “Second method” without this error?

+6
source share
3 answers

This is because you do not have qualified Cells(1, 1) with a worksheet object, and the same is 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

As an alternative:

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

EDIT: A Range object inherits a worksheet from Cells objects when code is run from a standard module or user form. If you use code from a worksheet code module, you will also need to qualify Range as follows:

 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
+8
source

This is because you do not fully qualify your cell object. try it

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

Pay attention to the DOT in front of the cells?

+8
source

For numerical addressing of cells, try enabling S1O1 in MS Excel settings. This is the second tab at the top (i.e. Formulas), somewhere in the middle of the page in my Hungarian version.

If enabled, it handles VBA addressing in both styles, that is, range ("A1: B10") and range (cells (1, 1), cells (10, 2)). I assume that it only handles the Range style ("A1: B10") if it is 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 addition .)

0
source

Source: https://habr.com/ru/post/954428/

More articles:


All Articles