Can VBA determine if a cell/range is in a group?

  • Thread starter Thread starter BlindGuardian
  • Start date Start date
Hi Ron,

In my case, I have several rows that have been grouped in an outline
i.e. a grey bar appears on the left with "+" and "-" characters that
allow me to expand and collapse the rows which belong to the group. The
visibility of these can change by setting the Tools --> Options... -->
View (tab) --> Outline symbols (checkbox).

What I want to know is if my selection is currently within one of those
rows which is in a group. I have not been able to find anything using
Excel/VBA help to discover a property that seems to indicate whether a
cell is part of a group or not. The nearest I've seen is IndentLevel
but that always returns 0.

Thanks for taking the time to respond.
 
IF you mean is the active cell selected part of a NAMED RANGE or another
group of somehow defined cells, then yes Look up vba help for the
INTERSECT property
 
You can use the Intersect method to determine whether a cell is
in another range of cells. For example,

Dim Rng As Range
Set Rng = Range("A1")
If Not Application.Intersect(Rng, Range("A1:A10")) Is Nothing
Then
' Rng is in A1:A10
Else
' Rng is not in A1:A10
End If


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"BlindGuardian"
<[email protected]>
wrote in message
news:[email protected]...
 
I think this may work for you: OutlineLevel

It will return 2, or more, if grouped in an outline. 1 if not
outlined.


Sub Test()

Dim IsGrouped As Boolean

IsGrouped = (ActiveCell.Rows.OutlineLevel > 1)

MsgBox "Grouped: " & IsGrouped

End Sub



Does that help?

Ron
 
Previously when I tried

ActiveCell.OutlineLevel

I kept getting an error that it could not retrieve the property from
the object. Now I see that OutlineLevel is a property that must be
applied for a Rows object.

Thanks very much for your help, Ron.
 
You're very welcome. I'm glad that worked for you. Thanks for updating
me

Regards,
Ron
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA set thick borders around non empty cells in range 0
Can Vba workout if a selection is part of a group? 0
Delete range names within a selection 13
How to Select A Cell in VBA with a Variable Row Number? 3
Excel VBA 1
Throwing #Value in a UDF written in Excel VBA 4
Autofill a dynamic range with a formula. 1
Counting Bordered Cells 5

Back
Top