Excel Fully Qualifying Range (Cells(),Cells()) [duplicate]

Question

I know many people have talked about fully qualifying ranges. I just want to know how deep you need to go with it to avoid problems.

In the example, I have qualified my worksheet using a dimensioned variable. Is it then necessary to be 100% reliable, to further qualify within the range parentheses?

dim myWS as Worksheet
set myWS = Thisworkbook.Activesheet

-- Use method A?

myWS.Range(Cells(1,5), Cells(500,20)).ClearContents

-- Or method B?

myWS.Range(myWS.Cells(1,5), myWS.Cells(500,20)).ClearContents

Does "Cells" inside Range parentheses default to reference myWS.Range in which it was called or does "Cells" default to reference the active sheet?

If myWS were set to a sheet other than the active sheet, would I get unexpected results from Cells?

It's not hard, except I will have to go back and change it in many places if you say that the second way is more trustworthy!

Answer 1

Method A will only work if your myWS is active at the time of being called. Method B is more robust, as are method C:

myWS.Range(Cells(1,5).Address, Cells(500,20).Address).ClearContents 

or method D:

myWS.Range("E1:T500")