Is it required to select the sheet before setting the range

[ThrottleWorks]

Hi,

I have a (silly) doubt.
I am trying to define a range. My problem is I need to select the particular sheet.

If the sheet is not selected then macro gives bug.

For example, the active sheet is "bbb" and I want to define range from sheet "aaa".

I need to select the "aaa" sheet first then define the code.

Worksheets("aaa").Select
Set Rng1 = Worksheets("aaa").Range(Cells(7, 3), Cells(Lr2, 3))

I wanted to know, if this is the correct way to do it.
Is there more efficient way to define range.

Can anyone please help me in this.

 

[Deepak]

No need to select.

Set Rng1 = Worksheets("aaa").Range(Cells(7, 3), Cells(Lr2, 3))

 

[ThrottleWorks]

Hi @Deepak , thanks a lot for the help ! I will use this from now.

PS - Nice Avtar (and hair style too) ! :D

 

[Deepak]

Thanks..

Well there are many ways to define range.

http://spreadsheetpage.com/index.php/tip/referring_to_ranges_in_your_vba_code/

 

[ThrottleWorks]

Hi Deepak,

Thanks for the help however I think I was trying the same thing before.
But it gave me bug.

Please refer below codes.

Mine
Worksheets("aaa").Select
Set Rng1 = Worksheets("aaa").Range(Cells(7, 3), Cells(Lr2, 3))

Yours
Set Rng1 = Worksheets("aaa").Range(Cells(7, 3), Cells(Lr2, 3))

I think both of us are using the same line. However if I do not select the sheet it still gives me bug.

Please correct me if I am wrong (and you have time).

Good night.

 

[Deepak]

Pls share the complete code.
So that it would be diagnosed

 

[p45cal]

A little more circumspection required;
If this line:
Set Rng1 = Worksheets("aaa").Range(Cells(7, 3), Cells(Lr2, 3))
is in a standard code module and the active sheet is bbb, it will raise an error.

If the same line is executed when the active sheet is aaa it will be fine.

If the line is in a sheet's code module and that sheet is:
1. Sheet aaa, it will be fine.
2. Sheet bbb, it will not be fine.

Why? Because the range being set is defined using two other ranges (Cells(7, 3) and Cells(Lr2, 3)) and neither of these is qualified, and so can refer to cells on a different sheet:
If in a standard module those ranges will be on the active sheet
If in a sheet's code module those ranges will be on that sheet, regardless of whether it's active or not.

So a safer way to set the range, so that it works regardless of where the code is and of which sheet is the active sheet, is to qualify those two ranges as well:

With Worksheets("aaa")
  Set Rng1 = .Range(.Cells(7, 3), .Cells(Lr2, 3))
End With

 

[Deepak]

Yes..this is what needs to be taken care.

 

[ThrottleWorks]

Hi @p45cal , thanks a lot for the help. I am checking this and will revert shortly.

Hi @deeepak , thanks for the help. Sorry could not share the code as of now. On a different machine...

Thanks to both Deepak and p45cal, it's working perfect now (with end with). Good night.

 

[Somendra Misra]

Hi Sachin,

Below code will work from any sheet..

Set rng1 = Worksheets("aaa").Range(Worksheets("aaa").Cells(7, 3), Worksheets("aaa").Cells(10, 3))

Regards,

 

[ThrottleWorks]

Hi @Somendra Misra Dada, it's working perfectly. Thanks for the help.

Good night.

My guess is below was the issue. Sheet BBB was active and I was trying to set range for Sheet AAA.

Set Rng1 = Worksheets("aaa").Range(Cells(7, 3), Cells(Lr2, 3))
is in a standard code module and the active sheet is bbb, it will raise an error.

 

[Deepak]

Another method to call from anywhere!!

Set Rng1 = Worksheets("aaa").Range("$C$7:$C$" & Lr2)

 

[ThrottleWorks]

Hi @Deepak Sir, it's working perfectly. Thanks for the help. Have a nice day ahead.