Assign Range - Inactive Worksheet
 

Hi guys,

I am simply trying to assign a range ("B2":to the bottom of column B
located in an inactive worksheet using the .End(xlDown) method.

Seems simple enough, but I keep hitting walls :confused:



Code
-------------------


Sub defineOrders2()

Dim rge1 As Range
Dim rge2 As Range
Dim cell1 As Variant
Dim cell2 As Variant

'This statement assigns a range that isolates the last cell in Column B

Set rge2 = Range(Worksheets("order").Range("B2").End(xlDown))

'But I want the range to begin at "B2" and flow to the last cell in B.


'I attempted to assign variables to cells so that...
cell1 = Worksheets("order").Range("B2")
cell2 = Worksheets("order").Range("B2").End(xlDown)


'But this statement fails
Set rge2 = Range(cell1,cell2)

End Sub



-------------------

--
jazzjav
-----------------------------------------------------------------------
jazzjava's Profile: http://www.excelforum.com/member.php...fo&userid=1969
View this thread: http://www.excelforum.com/showthread.php?threadid=37910

Assign Range - Inactive Worksheet
 

What's the actual problem?

Why not come from below?

Code
-------------------

Sub defineOrders2()
Dim rge2 As Range
Dim LastRow As Long

LastRow = Worksheets("order").Range("B65536").End(xlUp).Row
Set rge2 = Worksheets("order").Range("B2:B" & LastRow)

End Sub

-------------------

--
Nori
-----------------------------------------------------------------------
Norie's Profile: http://www.excelforum.com/member.php...fo&userid=1936
View this thread: http://www.excelforum.com/showthread.php?threadid=37910

Assign Range - Inactive Worksheet
 

'But this statement fails
Set rge2 = Range(cell1,cell2)

Because, unmodified, Range always refers to the active sheet. So this
should work:

Set rge2 = Worksheets("order").Range(cell1,cell2)


--
Jim
"jazzjava" wrote in
message ...
|
| Hi guys,
|
| I am simply trying to assign a range ("B2":to the bottom of column B)
| located in an inactive worksheet using the .End(xlDown) method.
|
| Seems simple enough, but I keep hitting walls :confused:
|
|
|
| Code:
| --------------------
|
|
| Sub defineOrders2()
|
| Dim rge1 As Range
| Dim rge2 As Range
| Dim cell1 As Variant
| Dim cell2 As Variant
|
| 'This statement assigns a range that isolates the last cell in Column B
|
| Set rge2 = Range(Worksheets("order").Range("B2").End(xlDown))
|
| 'But I want the range to begin at "B2" and flow to the last cell in B.
|
|
| 'I attempted to assign variables to cells so that...
| cell1 = Worksheets("order").Range("B2")
| cell2 = Worksheets("order").Range("B2").End(xlDown)
|
|
| 'But this statement fails
| Set rge2 = Range(cell1,cell2)
|
| End Sub
|
|
|
| --------------------
|
|
| --
| jazzjava
| ------------------------------------------------------------------------
| jazzjava's Profile:
http://www.excelforum.com/member.php...o&userid=19696
| View this thread: http://www.excelforum.com/showthread...hreadid=379102
|

Assign Range - Inactive Worksheet
 

Hi,

Thank you for your suggestions,

I attempted the following:


Code:
--------------------

Set rge2 = Worksheets("order").Range(cell1, cell2)
'Compiles OK but I get a RunTime Error: '1004' Application defined or object defined error


Set rge2 = Worksheets("order").Range("B2:B" & LastRow)
'Also compiles OK but I get the same RunTime Error: '1004' Application defined or object defined error



--------------------


--
jazzjava
------------------------------------------------------------------------
jazzjava's Profile: http://www.excelforum.com/member.php...o&userid=19696
View this thread: http://www.excelforum.com/showthread...hreadid=379102

Assign Range - Inactive Worksheet
 

try this

cell1 = Worksheets("order").Range("B2").Address
cell2 = Worksheets("order").Range("B2").End(xlDown).Addres s
Set rge2 = Range(cell1, cell2)
rge2.Select


--
anilsolipuram
------------------------------------------------------------------------
anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
View this thread: http://www.excelforum.com/showthread...hreadid=379102

Assign Range - Inactive Worksheet
 

Did you try the whole of the code I posted?

Code:
--------------------

Dim rge2 As Range
Dim LastRow As Long

LastRow = Worksheets("order").Range("B65536").End(xlUp).Row
Set rge2 = Worksheets("order").Range("B2:B" & LastRow)
--------------------


--
Norie
------------------------------------------------------------------------
Norie's Profile: http://www.excelforum.com/member.php...o&userid=19362
View this thread: http://www.excelforum.com/showthread...hreadid=379102

Assign Range - Inactive Worksheet
 

Since you declared Cell1 and cell2 as variants and didn't use Set in this
statement:

cell1 = Worksheets("order").Range("B2")

Meant that Cell1 contained the value in B2 of the Order worksheet.

I'd do something like:

Option Explicit
Sub defineOrders2A()

Dim rge1 As Range

with worksheets("order")
set rge1 = .range("b2",.cells(.rows.count,"B").end(xlup))
end with

End Sub

It actually starts at the bottom of column B and works its way up.



jazzjava wrote:

Hi guys,

I am simply trying to assign a range ("B2":to the bottom of column B)
located in an inactive worksheet using the .End(xlDown) method.

Seems simple enough, but I keep hitting walls :confused:

Code:
--------------------


Sub defineOrders2()

Dim rge1 As Range
Dim rge2 As Range
Dim cell1 As Variant
Dim cell2 As Variant

'This statement assigns a range that isolates the last cell in Column B

Set rge2 = Range(Worksheets("order").Range("B2").End(xlDown))

'But I want the range to begin at "B2" and flow to the last cell in B.


'I attempted to assign variables to cells so that...
cell1 = Worksheets("order").Range("B2")
cell2 = Worksheets("order").Range("B2").End(xlDown)


'But this statement fails
Set rge2 = Range(cell1,cell2)

End Sub



--------------------

--
jazzjava
------------------------------------------------------------------------
jazzjava's Profile: http://www.excelforum.com/member.php...o&userid=19696
View this thread: http://www.excelforum.com/showthread...hreadid=379102

--

Dave Peterson