Detect if a cell is within a group

[Guest]

Its possible to build a group of cells using Rangte.Group, but how is it
possible to detect if a cell is within a group or iterate through all members
of a group?
Thanks

 

[Dave Peterson]

It sounds like you don't want to use .group. I'm betting that you want to use
Union()

Like:

Dim myRng as range
with activesheet
set myrng = union(.range("a1:b9"),.range("c799"),.range("x1:z3"))
end with

Then you can use Intersect to see if a cell is in a range:

dim myCell as range
set mycell = activesheet.range("b33")
if intersect(mycell, myrng) is nothing then
'not in that range
else
'yep, it is
end if

 

[Guest]

Sorry, its another problem:
How ist it possible to detect that a user has build a group within excel
using the appropriate menu entry?
I want to check for a specific range using VBA if the cells has been
inserted into a group, i dont want to build a group using VBA, just check if
a call is within a group.

 

[Dave Peterson]

You mean Data|Group and Outline?

You can use VBA to look at the .outlinelevel

Option Explicit
Sub testme01()

Dim myRow As Range
Dim IsOutLineUsed As Boolean

IsOutLineUsed = False
For Each myRow In ActiveSheet.Range("A12:A25").entirerow.Rows
If myRow.OutlineLevel > 1 Then
IsOutLineUsed = True
Exit For
End If
Next myRow

MsgBox IsOutLineUsed

End Sub

This example looks at all the rows between 12:25 to see if data|group and
outline has been applied to it.