Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range problems...
Hello All,
If I have the following code: ------------------- Dim MyRange As Range Set MyRange = "B19:I26" ------------------- How can i get the Active worksheet (or any other worksheet) to select that same range. ActiveSheet.Range(MyRange).Select wont work because Range expects 2 arguments. TIA, Neil. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range problems...
The problem is that you're not properly using the Range data type and
the Set keyword. If you use MyRange as a Range object, it's only going to apply to the specific sheet and range you used when you set it (ActiveSheet by default). To generalize the range for all sheets, there are (at least) two ways to do it: First: --------------- Dim MyRange as String MyRange = "B19:I26" Worksheet(<Whatever).Range(MyRange).Select --------------- Second: --------------- Dim MyRange as Range Set MyRange = Range("B19:I26") Worksheet(<Whatever).Range(MyRange.Address).Selec t --------------- The first is probably best because you save a little memory (especially if it's in a loop). Let me know if that doesn't make sense. Mark Neil wrote: Hello All, If I have the following code: ------------------- Dim MyRange As Range Set MyRange = "B19:I26" ------------------- How can i get the Active worksheet (or any other worksheet) to select that same range. ActiveSheet.Range(MyRange).Select wont work because Range expects 2 arguments. TIA, Neil. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range problems...
"Neil" wrote in message ... Hello All, If I have the following code: ------------------- Dim MyRange As Range Set MyRange = "B19:I26" It will fail as you cannot set a range objcet to a string. You should use Set MyRange = Range("B19:I26") ------------------- How can i get the Active worksheet (or any other worksheet) to select that same range. Then you just use Activesheet.MyRange.Select ActiveSheet.Range(MyRange).Select wont work because Range expects 2 arguments. Where do you get that idea from? You have two syntaxes for Range Syntax 1 expression.Range(Cell1) Syntax 2 expression.Range(Cell1, Cell2) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range problems...
A few other points:
If you are talking about the active worksheet, you can just do Range().Select Unless you need to re-use your range address, you can specify it directly, e.g. Range("B19:I26").Select. Finally, you will not be able to use Range.Select on a sheet if it is not active; if ot talking about the active sheet, before you select you need to activate the sheet, e.g.: Worksheets("Sheet2").Activate Worksheets("Sheet2").Range("B19:I26").Select "Mark Bigelow" wrote: The problem is that you're not properly using the Range data type and the Set keyword. If you use MyRange as a Range object, it's only going to apply to the specific sheet and range you used when you set it (ActiveSheet by default). To generalize the range for all sheets, there are (at least) two ways to do it: First: --------------- Dim MyRange as String MyRange = "B19:I26" Worksheet(<Whatever).Range(MyRange).Select --------------- Second: --------------- Dim MyRange as Range Set MyRange = Range("B19:I26") Worksheet(<Whatever).Range(MyRange.Address).Selec t --------------- The first is probably best because you save a little memory (especially if it's in a loop). Let me know if that doesn't make sense. Mark Neil wrote: Hello All, If I have the following code: ------------------- Dim MyRange As Range Set MyRange = "B19:I26" ------------------- How can i get the Active worksheet (or any other worksheet) to select that same range. ActiveSheet.Range(MyRange).Select wont work because Range expects 2 arguments. TIA, Neil. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
problems editing data range | Excel Discussion (Misc queries) | |||
Range name problems | Excel Discussion (Misc queries) | |||
Macro problems with range and naming issues | Excel Programming | |||
Range problems | Excel Programming | |||
Variables and range problems | Excel Programming |