Grouping
 

I'm trying to figure out a way to basically create an auto updating
Advanced Filter for multiple filters. My current idea was to create
columns with my data called "Group1", "Group2" etc... which each have
a logic statement that gives a value of 1 for rows that should be
added to the group. Then I created a function, "myGroup()" that will
return and range of the rows referenced to the respective group. For
example:

row A B C G1 G2
1 2 3 6 1 1
2 4 6 12 0 1
3 6 9 18 0 1
4 8 12 24 0 1
5 10 15 30 0 1
6 12 18 36 0 0
7 14 21 42 0 0
8 16 24 48 1 0
9 18 27 54 1 0
10 20 30 60 1 1

therefore, basically myGroup("G1") gives range("$1:$1,$8:$10") and
myGroup("G2") gives range("$1:$1,$8:$10"). So far the function works
great. The way I use it is if I want to return the average of column
B for "G1" I simply use Average(B1:B10 myGroup("G1")), which
intercepts the two ranges and returns the average of 3,24,27, & 30,
which is exactly what I want.

My problem is that if I define a name, for example "B_Group" set to
=B1:B10 myGroup("G1"), Excel crashes as soon as I try to use the name.

So my question is, does anyone know off hand why Excel might be
crashing and/or is there another way to do what I am trying to do
here? Thanks for your help!!

Here is my function code----

Function myGROUP(filter As Variant, Optional rng As Range) As Range
Dim sht As Worksheet
Dim i As Long
Dim j As Long
Dim iStart() As Variant
Dim iEnd() As Variant
Dim AddressStr As String

Set sht = Application.Caller.Parent
If rng Is Nothing Then Set rng = sht.Range("GroupRNG") 'This is the
column of data that contains the filter logic

For i = 1 To rng.rows.Count

If rng(i).value = filter And Not (rng(i).OFFSET(-1).value = filter)
Then
ReDim Preserve iStart(j) As Variant
iStart(j) = rng(i).row
End If

If rng(i).value = filter And Not (rng(i).OFFSET(1).value = filter)
Then
ReDim Preserve iEnd(j) As Variant
iEnd(j) = rng(i).row
j = j + 1
End If
Next i

For j = LBound(iStart) To UBound(iStart)
AddressStr = AddressStr & "'" & sht.name & "'!$" & iStart(j) & ":$" &
iEnd(j) & IIf(j < UBound(iStart), " ,", "")
Next j

Set myGROUP = sht.Range(AddressStr)


End Function