Range refernce question...

[Guest]

If I am referring to a range not on the active sheet, why does this work
triggered from a Macro button on Sheet1...

Sheets("Sheet2").Range("A2:G57").ClearContents

But this will not?

Sheets("Sheet2").Range(Cells(2, 1), Cells(endrow, 7)).ClearContents

If I am on Sheet2, the above code works.

Why?

 

[Die_Another_Day]

Jay, Cells also has a parent object, which is a sheet. You are in
effect actually saying this:
Sheets("Sheet2").Range(ActiveSheet.Cells(2, 1),
ActiveSheet.Cells(endrow, 7)).ClearContents 'which makes no sense to
excel
This will work:
With Sheets("Sheet2")
.Range(.Cells(2,1),.Cells(endrow,7)).ClearContents
End With

Charles Chickering

 

[Guest]

Thanks!

Jay, Cells also has a parent object, which is a sheet. You are in
effect actually saying this:
Sheets("Sheet2").Range(ActiveSheet.Cells(2, 1),
ActiveSheet.Cells(endrow, 7)).ClearContents 'which makes no sense to
excel
This will work:
With Sheets("Sheet2")
.Range(.Cells(2,1),.Cells(endrow,7)).ClearContents
End With

Charles Chickering

 

[Bob Phillips]

If you are referencing another sheet, you have to qualify all objects with
the sheet identifier, otherwise part of the statement refers to the other
sheet, part refers to the active sheet.

Use

Sheets("Sheet2").Range(Sheets("Sheet2")..Cells(2, 1),
Sheets("Sheet2")..Cells(endrow, 7)).ClearContents

or better

With Sheets("Sheet2")
.Range(.Cells(2, 1), .Cells(endrow, 7)).ClearContents
End With

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Alan Beban]

If I am referring to a range not on the active sheet, why does this work
triggered from a Macro button on Sheet1...

Sheets("Sheet2").Range("A2:G57").ClearContents

But this will not?

Sheets("Sheet2").Range(Cells(2, 1), Cells(endrow, 7)).ClearContents

If I am on Sheet2, the above code works.

Why?

Assuming endrow=57, because Cells(2,1) and Cells(endrow,7) are, by
default, referring to cells on the active sheet.

There are several ways to avoid the problem. Perhaps the clearest that
indicates the difficulty is

Sheets("Sheet2").Range(Sheets("Sheet2").Cells(2, 1), _
Sheets("Sheet2").Cells(endrow, 7)).ClearContents

A common fix is

With Sheets("Sheet2")
..Range(.Cells(2, 1), .Cells(endrow, 7)).ClearContents
End With

I prefer

Set rng = Sheets("Sheet2").Range("A1")
Range(rng(2, 1), rng(endrow, 7)).ClearContents

Alan Beban