..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" wrote in message
...
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