Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 | | | | 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 | |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I email an inactive copy of a worksheet | Excel Discussion (Misc queries) | |||
Can I freeze pane on an inactive worksheet? | Excel Programming | |||
macro can't form a Range on an inactive Worksheet | Excel Programming | |||
macro can't form a Range on an inactive Worksheet | Excel Programming | |||
How to get the activecell of inactive worksheet ? | Excel Programming |