Declaring a Range Object
 

This is what I need:
---------------------------
I am working in worksheet 1.I would like to declare a range calle
My_Range in worksheet 2.

The cells that make up the range will be known only at run time. ie M
range will have n number of rows where n will be determined when th
program runs.


How do i do this?


So far this is what I've done
-----------------------------------

Set My_Range = Range(Range("a1"), Range("a1").Offset(n))

This works but it selects the range from the current worksheet i
sheet1.


When I try this:
--------------------

Set My_Range = Range(Worksheets.("sheet2").Range("a1")
ksheets.("sheet2").Range("a1").Offset(n))

It gives me an error.

Help! I need to finish this asap for an assignment.

Pooja

--
Message posted from http://www.ExcelForum.com

Declaring a Range Object
 

Hi
try something like the following

Dim wks as worksheet
dim my_range as range
set wks = activeworkbook.worksheets("Sheet2")
set my_range=wks.range(cells(1,1),cells(n,1))


--
Regards
Frank Kabel
Frankfurt, Germany


This is what I need:
---------------------------
I am working in worksheet 1.I would like to declare a range called
My_Range in worksheet 2.

The cells that make up the range will be known only at run time. ie
My
range will have n number of rows where n will be determined when the
program runs.


How do i do this?


So far this is what I've done
-----------------------------------

Set My_Range = Range(Range("a1"), Range("a1").Offset(n))

This works but it selects the range from the current worksheet ie
sheet1.


When I try this:
--------------------

Set My_Range = Range(Worksheets.("sheet2").Range("a1"),
ksheets.("sheet2").Range("a1").Offset(n))

It gives me an error.

Help! I need to finish this asap for an assignment.

Pooja.


---
Message posted from http://www.ExcelForum.com/

Declaring a Range Object
 

Set My_Range = Sheets("Sheet2").Range("A1:A" & n)
Or for whole rows:
Set My_Range = Sheets("Sheet2").Rows("1:" & n

----- pooja wrote: ----

This is what I need
--------------------------
I am working in worksheet 1.I would like to declare a range calle
My_Range in worksheet 2.

The cells that make up the range will be known only at run time. ie M
range will have n number of rows where n will be determined when th
program runs


How do i do this


So far this is what I've don
----------------------------------

Set My_Range = Range(Range("a1"), Range("a1").Offset(n)

This works but it selects the range from the current worksheet i
sheet1


When I try this
-------------------

Set My_Range = Range(Worksheets.("sheet2").Range("a1")
ksheets.("sheet2").Range("a1").Offset(n)

It gives me an error

Help! I need to finish this asap for an assignment

Pooja


--
Message posted from http://www.ExcelForum.com

Declaring a Range Object
 

If I understand you correctly:

Dim My_Range As Range
Set My_Range = Worksheets("Sheet2").Range("A1").Resize(n, 1)


In article ,
pooja wrote:

This is what I need:
---------------------------
I am working in worksheet 1.I would like to declare a range called
My_Range in worksheet 2.

The cells that make up the range will be known only at run time. ie My
range will have n number of rows where n will be determined when the
program runs.


How do i do this?


So far this is what I've done
-----------------------------------

Set My_Range = Range(Range("a1"), Range("a1").Offset(n))

This works but it selects the range from the current worksheet ie
sheet1.


When I try this:
--------------------

Set My_Range = Range(Worksheets.("sheet2").Range("a1"),
ksheets.("sheet2").Range("a1").Offset(n))

It gives me an error.

Help! I need to finish this asap for an assignment.

Pooja.


---
Message posted from http://www.ExcelForum.com/

Declaring a Range Object
 

n = 7
Set rng = Sheets(2).Range("A1")
Set my_Range = Range(rng(1), rng(n))

Alan Beban

pooja < wrote:
This is what I need:
---------------------------
I am working in worksheet 1.I would like to declare a range called
My_Range in worksheet 2.

The cells that make up the range will be known only at run time. ie My
range will have n number of rows where n will be determined when the
program runs.


How do i do this?


So far this is what I've done
-----------------------------------

Set My_Range = Range(Range("a1"), Range("a1").Offset(n))

This works but it selects the range from the current worksheet ie
sheet1.


When I try this:
--------------------

Set My_Range = Range(Worksheets.("sheet2").Range("a1"),
ksheets.("sheet2").Range("a1").Offset(n))

It gives me an error.

Help! I need to finish this asap for an assignment.

Pooja.


---
Message posted from http://www.ExcelForum.com/

Declaring a Range Object
 

This will cause a run-time error if Sheet2 is not active.

Cells() defaults to the active worksheet, so this is the equivalient of

Dim wks as worksheet
dim my_range as range
set wks = activeworkbook.worksheets("Sheet2")
set my_range=wks.range(ActiveSheet.cells(1,1),ActiveSh eet.cells(n,1))


Instead, try:

Dim My_Range As Range
With ActiveWorkbook.Worksheets("Sheet2")
Set My_Range = .Range(.Cells(1, 1), .Cells(n, 1))
End With

or, a bit simpler:

Dim My_Range As Range
Set My_Range = Worksheets("Sheet2").Range("A1").Resize(n, 1)



In article ,
"Frank Kabel" wrote:

Dim wks as worksheet
dim my_range as range
set wks = activeworkbook.worksheets("Sheet2")
set my_range=wks.range(cells(1,1),cells(n,1))

Declaring a Range Object
 

Hi JE
I always forget the worksheet identifier for the cells object :-(
thanks for your correction :-)

--
Regards
Frank Kabel
Frankfurt, Germany


JE McGimpsey wrote:
This will cause a run-time error if Sheet2 is not active.

Cells() defaults to the active worksheet, so this is the equivalient
of

Dim wks as worksheet
dim my_range as range
set wks = activeworkbook.worksheets("Sheet2")
set
my_range=wks.range(ActiveSheet.cells(1,1),ActiveSh eet.cells(n,1))


Instead, try:

Dim My_Range As Range
With ActiveWorkbook.Worksheets("Sheet2")
Set My_Range = .Range(.Cells(1, 1), .Cells(n, 1))
End With

or, a bit simpler:

Dim My_Range As Range
Set My_Range = Worksheets("Sheet2").Range("A1").Resize(n, 1)



In article ,
"Frank Kabel" wrote:

Dim wks as worksheet
dim my_range as range
set wks = activeworkbook.worksheets("Sheet2")
set my_range=wks.range(cells(1,1),cells(n,1))

Declaring a Range Object
 

You received better answers, but for interest, you second attempt was headed
in the right direction - you just had a syntax error:

Sub Tester6()
n = 7
Set My_Range = Range(Worksheets("sheet2").Range("a1"), _
Worksheets("sheet2").Range("a1").Offset(n))


MsgBox My_Range.Address(external:=True)
End Sub

works.

--
Regards,
Tom Ogilvy


"pooja " wrote in message
...
This is what I need:
---------------------------
I am working in worksheet 1.I would like to declare a range called
My_Range in worksheet 2.

The cells that make up the range will be known only at run time. ie My
range will have n number of rows where n will be determined when the
program runs.


How do i do this?


So far this is what I've done
-----------------------------------

Set My_Range = Range(Range("a1"), Range("a1").Offset(n))

This works but it selects the range from the current worksheet ie
sheet1.


When I try this:
--------------------

Set My_Range = Range(Worksheets.("sheet2").Range("a1"),
ksheets.("sheet2").Range("a1").Offset(n))

It gives me an error.

Help! I need to finish this asap for an assignment.

Pooja.


---
Message posted from http://www.ExcelForum.com/

Declaring a Range Object
 

Frank Kabel wrote:

Hi JE
I always forget the worksheet identifier for the cells object :-(

That might be a good reason to systematically avoid the "cells object".
I.e.,

set rng = Worksheets("whatever").range("A1")

rng then carries its qualification with it.

Alan Beban

Declaring a Range Object
 

No reason to abandon the Cells property. Just qualify it.

Sub testIt4()
Dim aRng As Range, n As Integer
n = 7
With Worksheets(2)
Set aRng = Range(.Cells(1, 1), .Cells(n, 1))
End With
MsgBox aRng.Parent.Name & "," & aRng.Address
End Sub

or, if using the equivalent of SHIFT+CTRL+down arrow:

With Worksheets(2).Cells(1, 1)
Set aRng = Range(.Item(1), .End(xlDown))


--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Hi JE
I always forget the worksheet identifier for the cells object :-(
thanks for your correction :-)

Declaring a Range Object
 

Only reason I can think of is that

Set aRng = Worksheets(2).Cells(1, 1).Resize(n, 1)

is faster than the additional reference resolution in

With Worksheets(2)
Set aRng = .Range(.Cells(1, 1), .Cells(n, 1))
End With

In article <MPG.1b1bb58da25e61729897ed@news-server,
Tushar Mehta wrote:

No reason to abandon the Cells property.

Declaring a Range Object
 

Hi JE,

I was responding to a comment that I mistakenly attributed to Frank
('That might be a good reason to systematically avoid the "cells
object".)' Actually, the quote is from Alan's response to Frank.

Note, though, that neither are you abandoning the Cells property; just
using it once (with a Resize) rather than twice.

Somebody at some point posted an ad hoc analysis of how long it took to
reference a cell using each of six or seven different methods. But,
since I don't usually worry about computer speed, I couldn't give you a
reference without some amount of digging.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Only reason I can think of is that

Set aRng = Worksheets(2).Cells(1, 1).Resize(n, 1)

is faster than the additional reference resolution in

With Worksheets(2)
Set aRng = .Range(.Cells(1, 1), .Cells(n, 1))
End With

In article <MPG.1b1bb58da25e61729897ed@news-server,
Tushar Mehta wrote:

No reason to abandon the Cells property.

Declaring a Range Object
 

Another reason is that

Set bRng = Worksheets(2).Range("A1"),
Set aRng = Range(bRng(1,1), bRng(n,1))

is faster as well.

And, if looping or multiple uses are required in the application, after
Set bRng = Worksheets(2).Range("A1"),

Set aRng = Range(bRng(1,1), bRng(n,1)) is faster than
Set aRng = Worksheets(2).Cells(1, 1).Resize(n, 1)

Alan Beban

JE McGimpsey wrote:

Only reason I can think of is that

Set aRng = Worksheets(2).Cells(1, 1).Resize(n, 1)

is faster than the additional reference resolution in

With Worksheets(2)
Set aRng = .Range(.Cells(1, 1), .Cells(n, 1))
End With

In article <MPG.1b1bb58da25e61729897ed@news-server,
Tushar Mehta wrote:


No reason to abandon the Cells property.

Declaring a Range Object
 

Tushar Mehta wrote:

No reason to abandon the Cells property. Just qualify it.

Well, the reason I suggested abandoning it is that the OP said he has
difficulty *remembering* to qualify it, so "just qualify it" is not much
help. And the fact remains that

Set bRng = Worksheets(2).Range("A1")
Set aRng = Range(bRng(1,1),bRng(n,1))

is faster than

With Worksheets(2)
Set aRng = Range(.Cells(1, 1), .Cells(n, 1))
End With

After stumbling onto the then undocumented (as far as I could ascertain)
direct indexing of a range object variable many years ago, I've never
understood why so many people cling to and proliferate the relatively
more cumbersome and inefficient Cells property in lieu of it.

Alan Beban

Sub testIt4()
Dim aRng As Range, n As Integer
n = 7
With Worksheets(2)
Set aRng = Range(.Cells(1, 1), .Cells(n, 1))
End With
MsgBox aRng.Parent.Name & "," & aRng.Address
End Sub

or, if using the equivalent of SHIFT+CTRL+down arrow:

With Worksheets(2).Cells(1, 1)
Set aRng = Range(.Item(1), .End(xlDown))