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

nbrcrunch

Well-known Member
Joined
Jan 1, 2003
Messages
2,062
Well, I'm nearly done reading MrExcel On Excel. I'm on page 429 (but I still don't know "who done it." :) :rolleyes: )

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?
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,029
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:

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

DRJ

MrExcel MVP
Joined
Feb 17, 2002
Messages
3,853
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

Well-known Member
Joined
Jan 1, 2003
Messages
2,062
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

MrExcel MVP
Joined
Feb 17, 2002
Messages
3,853

ADVERTISEMENT

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

New Member
Joined
Dec 28, 2013
Messages
3
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

MrExcel MVP
Joined
May 7, 2008
Messages
21,829
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

Code:
    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

New Member
Joined
Dec 28, 2013
Messages
3
Assuming 'Point Template'!F1 contains "MPS-0 (2)" (sans quotes), these two lines should be equivalent:

Code:
    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

MrExcel MVP
Joined
May 7, 2008
Messages
21,829
Office Version
  1. 2010
Platform
  1. Windows
You're welcome.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,876
Messages
5,766,875
Members
425,383
Latest member
IllDo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top