When to Activate?

A

Antonio

The following works fine

Worksheets("EQ by P").Rows((number_s+ 4) & ":" & (number_ + 3)).ClearContents

The following does not (one line)
Worksheets("pivot").Range(Cells(number_s + 3, 1), Cells(number_ + 2,
5)).ClearContents

The following does (two lines):
Worksheets("pivot").Activate

Range(Cells(number_s + 3, 1), Cells(number_ + 2, 5)).ClearContents

Why is that?

When is "Activate" required?



Why is the following not working?
 
C

Chip Pearson

Antonio,

In the statement


Worksheets("pivot").Range(Cells(number_s + 3, 1), _
Cells(number_ + 2, 5)).ClearContents

The Cells property is NOT refering the cells on the Pivot
worksheet. They are refering to cells on the Active Sheet. If the
"Pivot" worksheet is not the Active Sheet, you'll get an Error
1004. You need to make the Cells properties reference the Pivot
sheet. Use a With statement as follows:

With Worksheets("pivot")
.Range(.Cells(number_s + 3, 1), _
.Cells(number_ + 2, 5)).ClearContents
End With


Note the "." character before "Range" and both "Cells".

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
A

Antonio

Hi Chip,

Understood.

I use the With extensively.

I tried to shorten the code and the dots and overlooked that.

Many thanks for the explanation that is very relevant.

Regards,

Antonio
 
A

Antonio

Hi again,

One thing though.

Shouldn't the following line have the same potential problem?

Worksheets("EQ by P").Rows((number_s+ 4) & ":" & (number_ + 3)).ClearContents

The question is that I have used a similar one for different worksheets,
that are not the active sheet, and they all work fine. Here the Rows()
property does seem to refer to the worksheet specified at the beginning of
the object.

What am I missing.

Many thanks,

Antonio
 
C

Chip Pearson

No, it should not have the same problem. You're referencing the
Rows property of the worksheet EQ by P. There is no stray
property that would be referencing the active sheet by default.
 
D

Dave Peterson

..Rows() belongs to the "EQ by P" worksheet.

But those other things are just numbers (I'm guessing).

It's kind of like writing:
Worksheets("EQ by P").Rows("12:15").ClearContents

Objects belong to something (ranges have a parent of the worksheet, worksheets
have a parent of the workbook, and workbooks have a parent of the application).

But numbers are just plain old numbers--they don't belong to any object.

===
1.2
the 2 doesn't really belong to the 1 <vbg>.
 
Top