Is it possible to get a Union of ranges over multiple sheets? Something like:

Set RngA = Sheets(1).Range("A1:A5")
Set RngB = Sheets(2).Range("B2:B10")
Set Rng = Union(RngA, RngB)

The above code give a "Method 'Union' of object '_Global' failed" Error, ie
You can't Union ranges from multiple sheets. Does anyone know a workaround
to this?

Tx,
Randall

In the Excel object model sheets are the containers for range objects. This
means that a range object can not span more than one sheet (they are
contained within the sheet). The only work around is to deal with the range
objects seperately. What exactly are you trying to do?
--
HTH...

Jim Thomlinson


"Randall" wrote:

Is it possible to get a Union of ranges over multiple sheets? Something like:

Set RngA = Sheets(1).Range("A1:A5")
Set RngB = Sheets(2).Range("B2:B10")
Set Rng = Union(RngA, RngB)

The above code give a "Method 'Union' of object '_Global' failed" Error, ie
You can't Union ranges from multiple sheets. Does anyone know a workaround
to this?

Tx,
Randall

I have data on multiple sheets for which I want to calculate median and
standard deviation. I am trying to use the worksheet functions (Median and
Stdev) to do the calculation.

The only work around I know of is to read in all my values and then write my
own median and standard deviation functions. Any better ideas?

Randall


"Jim Thomlinson" wrote:

In the Excel object model sheets are the containers for range objects. This
means that a range object can not span more than one sheet (they are
contained within the sheet). The only work around is to deal with the range
objects seperately. What exactly are you trying to do?
--
HTH...

Jim Thomlinson


"Randall" wrote:

Is it possible to get a Union of ranges over multiple sheets? Something like:

Set RngA = Sheets(1).Range("A1:A5")
Set RngB = Sheets(2).Range("B2:B10")
Set Rng = Union(RngA, RngB)

The above code give a "Method 'Union' of object '_Global' failed" Error, ie
You can't Union ranges from multiple sheets. Does anyone know a workaround
to this?

Tx,
Randall

The STDEV and MEDIAN worksheetfunctions can take multiple ranges from different sheets:

Set rngA = Sheets(1).Range("A1:A5")
Set rngb = Sheets(2).Range("B2:B10")
MsgBox "Standard Deviation is " & Application.StDev(rngA, rngb)
MsgBox "Median is " & Application.Median(rngA, rngb)

will do what you want.

HTH,
Bernie
MS Excel MVP


"Randall" wrote in message
...
I have data on multiple sheets for which I want to calculate median and
standard deviation. I am trying to use the worksheet functions (Median and
Stdev) to do the calculation.

The only work around I know of is to read in all my values and then write my
own median and standard deviation functions. Any better ideas?

Randall


"Jim Thomlinson" wrote:

In the Excel object model sheets are the containers for range objects. This
means that a range object can not span more than one sheet (they are
contained within the sheet). The only work around is to deal with the range
objects seperately. What exactly are you trying to do?
--
HTH...

Jim Thomlinson


"Randall" wrote:

Is it possible to get a Union of ranges over multiple sheets? Something like:

Set RngA = Sheets(1).Range("A1:A5")
Set RngB = Sheets(2).Range("B2:B10")
Set Rng = Union(RngA, RngB)

The above code give a "Method 'Union' of object '_Global' failed" Error, ie
You can't Union ranges from multiple sheets. Does anyone know a workaround
to this?

Tx,
Randall

Nope, that would seem to be the best way to do it.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Randall" wrote in message
...
I have data on multiple sheets for which I want to calculate median and
standard deviation. I am trying to use the worksheet functions (Median
and
Stdev) to do the calculation.

The only work around I know of is to read in all my values and then write
my
own median and standard deviation functions. Any better ideas?

Randall


"Jim Thomlinson" wrote:

In the Excel object model sheets are the containers for range objects.
This
means that a range object can not span more than one sheet (they are
contained within the sheet). The only work around is to deal with the
range
objects seperately. What exactly are you trying to do?
--
HTH...

Jim Thomlinson


"Randall" wrote:

Is it possible to get a Union of ranges over multiple sheets?
Something like:

Set RngA = Sheets(1).Range("A1:A5")
Set RngB = Sheets(2).Range("B2:B10")
Set Rng = Union(RngA, RngB)

The above code give a "Method 'Union' of object '_Global' failed"
Error, ie
You can't Union ranges from multiple sheets. Does anyone know a
workaround
to this?

Tx,
Randall