VBA syntax: Sheets().Range()...

[nbrcrunch]

Well, I'm nearly done reading MrExcel On Excel. I'm on page 429 (but I still don't know "who done it." )

I came across a snippet of code (that I extrapolated) to delete rows of data.

Sheets("Consolidation").Range(Sheets("Consolidation").Range("A1"), Sheets("Consolidation").Range("A1").End(xlDown)).EntireRow.Delete

The code works BUT: I noticed that RANGE fully qualifies the reference, including the sheet object. It seemed redundant to me to use the Sheets reference at the beginning of the syntax so I altered the line of code to read:

Range(Sheets("Consolidation").Range("A1"), Sheets("Consolidation").Range("A1").End(xlDown)).EntireRow.Delete

This also works.

My question: Is there a reason why the recommendation was made to include the Sheets at the beginning of the statement?

 

[tusharm]

In this case, you are right. But in general, check XL VBA help for what happens with the Range method is not qualified with an object.

Keeping in mind that I don't have the full context for the code, it could possibly be shortened (and maintenance simplified) with the untested:

with Sheets("Consolidation").Range("A1")
.Range(.cells(1), .End(xlDown)).EntireRow.Delete 
    end with

 

[DRJ]

If you don't state what sheet at the beginning excel will assume it is the active sheet, but what if you don't want it to be the active sheet. For example:

sheets("Sheet1").range("A1").value = "Test"

if you run this, nomatter what sheet is currently active it will still put the value in the right spot.

But

range("A1").value = "Test"

This will put the value in A1 of the current sheet, which may not be what you want, let say Sheet2 is active, this would put the value in Sheet2.

I always reference the sheet even if it is not needed. It makes it a lot easier when you want to change a program months after you completed it.

Also if you are dealing with multiple workbooks it is always a good idea to use ThisWorkBook to reference the workbook with the macro since that workbook may not always be active.

So

ThisWorkook.Sheets("Sheet1").range("A1").value = "Test"

Basically the more shortcuts you take in the code, the more chance there is of a problem occuring.

 

[nbrcrunch]

tusharm: thanks for re-assurance AND for the alternate statement construct.

DJR: But that was the whole basis of my question. Take a look at the Range statement I used -- it fully qualifies the worksheet and range reference. And again, both syntaxes work exactly the same, regardless of which worksheet is active.

 

[DRJ]

You dont need to put the sheet after the range statement like range(sheets(... that is just more work. It will work, but is not necessary. Start with the parent object then move to dependants and it will make more sense.

Like

Workbooks("MyWorkBook").Sheets("Sheet1").range("A1")


You will find that with excel and vba there are many different ways to do the same thing.

 

[ydnar185]

If you don't state what sheet at the beginning excel will assume it is the active sheet, but what if you don't want it to be the active sheet. For example:

sheets("Sheet1").range("A1").value = "Test"

if you run this, nomatter what sheet is currently active it will still put the value in the right spot.

But

range("A1").value = "Test"

This will put the value in A1 of the current sheet, which may not be what you want, let say Sheet2 is active, this would put the value in Sheet2.

I always reference the sheet even if it is not needed. It makes it a lot easier when you want to change a program months after you completed it.

Also if you are dealing with multiple workbooks it is always a good idea to use ThisWorkBook to reference the workbook with the macro since that workbook may not always be active.

So

ThisWorkook.Sheets("Sheet1").range("A1").value = "Test"

Basically the more shortcuts you take in the code, the more chance there is of a problem occuring.

Hi,
I am trying to use the sheets(" ") to reference a cell that contains a sheet name.
My exact code that i have that works, looks like this:
Sheets("MPS-0 (2)").Range(Sheets("Point Template").Range("k1")) = Sheets("Point Template").Range("i1").Value

but i want the "MPS-0 (2)" to actually reference a cell in a sheet.
I think it should look like:
Sheets(sheets("Point Template").range("f1")).Range(Sheets("Point Template").Range("k1")) = Sheets("Point Template").Range("i1").Value

but it does not work. Is it a syntax error, or am I going about it the wrong way?

Thanks in advance

 

[shg]

Assuming 'Point Template'!F1 contains "MPS-0 (2)" (sans quotes), these two lines should be equivalent:

    With Worksheets("Point Template")
               Worksheets("MPS-0 (2)").Range(.Range("K1").Value) = .Range("I1").Value
        Worksheets(.Range("F1").Value).Range(.Range("K1").Value) = .Range("I1").Value
    End With

 

[ydnar185]

Assuming 'Point Template'!F1 contains "MPS-0 (2)" (sans quotes), these two lines should be equivalent:

    With Worksheets("Point Template")
               Worksheets("MPS-0 (2)").Range(.Range("K1").Value) = .Range("I1").Value
        Worksheets(.Range("F1").Value).Range(.Range("K1").Value) = .Range("I1").Value
    End With

Worked perfect first time. THANKS!

 

[shg]

You're welcome.