When to Activate?

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?

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




"Antonio" <[email protected]> wrote in message
news:[email protected]...
> 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?
>

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

"Chip Pearson" wrote:

> 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
>
>
>
>
> "Antonio" <[email protected]> wrote in message
> news:[email protected]...
> > 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?
> >
>
>
>

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
"Antonio" wrote:

> 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
>
> "Chip Pearson" wrote:
>
> > 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
> >
> >
> >
> >
> > "Antonio" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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?
> > >
> >
> >
> >

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.


"Antonio" <[email protected]> wrote in message
news:[email protected]...
> 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
> "Antonio" wrote:
>
>> 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
>>
>> "Chip Pearson" wrote:
>>
>> > 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
>> >
>> >
>> >
>> >
>> > "Antonio" <[email protected]> wrote in
>> > message
>> > news:[email protected]...
>> > > 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?
>> > >
>> >
>> >
>> >

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

Antonio wrote:
>
> 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
> "Antonio" wrote:
>
> > 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
> >
> > "Chip Pearson" wrote:
> >
> > > 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
> > >
> > >
> > >
> > >
> > > "Antonio" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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?
> > > >
> > >
> > >
> > >

--

Dave Peterson