Declaring a Range Object

[pooja]

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

 

[Frank Kabel]

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))

 

[chris]

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

 

[JE McGimpsey]

If I understand you correctly:

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

 

[Alan Beban]

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

Alan Beban

 

[JE McGimpsey]

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),ActiveSheet.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)

 

[Frank Kabel]

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

 

[Tom Ogilvy]

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.

 

[Alan Beban]

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

 

[Tushar Mehta]

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

 

[JE McGimpsey]

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

 

[Tushar Mehta]

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

 

[Alan Beban]

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

 

[Alan Beban]

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