clearing a range to blanks

[Brian.Crawford]

I was trying to clear a range using

Sheets("theSheet").range(cells(5,tcol),cells(trow,tcol))=""
(where trow & tcol are previously defined)

this gives me an error, but the following works

Sheets("theSheet").range(range(cells(5,tcol),cells(trow,tcol)).address)=""

if I debug/print range(cells(5,tcol),cells(trow,tcol)).address
it gives me the right range for range(cells(5,tcol),cells(trow,tcol))
so why doesn't the first one work.

thanks
Brian

 

[Jonmo1]

When using this syntax

Range(Cells(w,x), Cells(y,z))

And you specify the Sheet on the RANGE part, you must also specify the sheet on the Cells part.

So instead of
Sheets("Sheet1").Range(Cells(w,x), Cells(y,z))

you need to use

Sheets("Sheet1").Range(Sheets("Sheet1").Cells(w,x), Sheets("Sheet1").Cells(y,z))


Because....

If the sheet is not specified, then VBA uses whatever sheet happens to be currently active.
Or if the code is in a Sheet Module, then it refers to That sheet.

So if say Sheet5 is currently active, and you use
Sheets("Sheet1").Range(Cells(w,x), Cells(y,z))
Then the RANGE part is referring to Sheet1, but both Cells parts are reeferring to Sheet5.


Hope that helps.

 

[Brian.Crawford]

so by using the form

Sheets("theSheet").range(range(cells(5,tcol),cells(trow,tcol)).address)=""

where I effectively force only the cells address part of the range(...) along with the sheet I get the same affect ... correct? (this does seem to work wherever I've used it but do I need to be concerned it may not?)

In any case I now understadn why the original didn't work

thanks for the explaination
Brian

 

[Jonmo1]

Maybe some color coding will help to see why that syntax works

Sheets("theSheet").range(range(cells(5,tcol),cells(trow,tcol)).address)

The blue text is a command independant from the Red text
The blue text does not refer to any sheet (in either the range or cells), so it does not error.
And it returns a text string like $A$5:$F$20 or whatever...
So by the time the Red text is reading it, it no longer has any cells or range commands to interprait, just the text string.

Nothing wrong with writing it that way, but it's a good "Best practice" to always explicitly reference the sheet on both range and cells commands.

It can be made easier by using With

With Sheets("Sheet1")
    .Range(.Cells(w,x), .Cells(y,z)).Value = ""
    'Notice the red dots on the Range and 2 Cells commands.
End With

Hope that helps.

 

[Brian.Crawford]

That's perfect (and "cleaner" too)
thanks a lot
Brian