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.

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

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.

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

"Neil" <neil.haywood@alpertonltd.com> wrote in message
news:d1utbu$dd$1@hercules.btinternet.com...
> 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)