Union over multiple sheets

[Guest]

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

 

[Guest]

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?

 

[Guest]

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

 

[Bernie Deitrick]

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

 

[Bob Phillips]

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

--

HTH

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

 

[Guest]

Yes, that answers the question as I posted it. Let me see if you can help
with a follow up question.

The number of sheet containing data is variable (anywhere from 1 to 50
sheets). My plan was to Union all the ranges together:

For i = 1 to N 'N is the number of Sheet containing data
If i = 1 Then
AllRange = Sheets(i).Range("A1:A5")
Else
Rng = Sheets(i).Range("A1:A5")
AllRange = Union(AllRange,Rng)
End If
Next i

'The Resulting AllRange is for N sheets

X = Application.WorksheetFunction.Median(AllRange)

The problem using .Median(RngA, RngB, etc.) is that for a give case I will
not know how many Ranges (Sheets) make up the collection. I suppose I could
have a huge set of if statements from 1 to 50. Any better ideas?

Thanks for your answer to my previous question and any future help you may
provide.

Randall

 

[Bernie Deitrick]

Randall,

That many sheets is a problem using the technique that I showed. But not a problem overall. I would
simply use a temporary sheet:

Sub CalcMedianAndStDev()
Dim N As Integer
Dim i As Integer
Dim mySht As Worksheet

N = Worksheets.Count

Set mySht = Worksheets.Add
mySht.Move After:=Worksheets(N + 1)

For i = 1 To N 'N is the number of Sheet containing data
Sheets(i).Range("A1:A5").Copy _
mySht.Range("A65536").End(xlUp)(2)
Next i

MsgBox Application.Median(mySht.Range("A65536").End(xlUp).CurrentRegion)
MsgBox Application.StDev(mySht.Range("A65536").End(xlUp).CurrentRegion)

Application.DisplayAlerts = False
mySht.Delete
Application.DisplayAlerts = True
End Sub


HTH,
Bernie
MS Excel MVP

 

[Guest]

Thank you for the help.

Just FYI, I found another work around by putting strings together for the
formula.

MyString = "=MEDIAN("
For i = 1 to N
C = ...
MyString = MyString & "'" & Sheet(i).Name & "'!C" & C
Next i

MyString =Left(MyString,Len(MyString)-1) & ")" 'Remove Last , Add a )
Range("A1").FormulaR1C1 = MyString

 

[Bernie Deitrick]

Randall,

Just FYI, that will blow up when you add up too many ranges - the limit is either based on
characters or ranges, depending on your exact approach, which is why I did not use that technique
for your 50 sheets.

HTH,
Bernie
MS Excel MVP