How to expand a group in Excel by using Hyperlink(

Question

I have a table at the top of my sheet and this table has a different section names. I'd like to insert a hyperlink to these section names to go and open it's group below when I click them.

Please Refer to the view of my table and sections as default (Collapsed)

I could create a macro which:

Expands all groups
Goes to the Section that I clicked,
Collapses all groups
Only opens the group on active cell, 

But assigning this macro to ~20 different sections increases the file size.

After some search I found this on SO: Excel: Assign a macro to a hyperlink? So maybe there is a way to connect this two method?

How this can be solved?

Answer 1

I'd suggest creating a master sheet with the "group" table and any rollups you need. The subsequent sheets could have all the "section" data on them. This has the added benefit of being more scaleable.

Is it strictly necessary to have all the information on the same sheet? This is pretty much why Excel has multiple sheets. Using multiple sheets would also allow you to use standard hyperlinks.

However, if you would like some VBA to get you closer, consider the code below. This grabs the value form the active cell, then searches for the next cell with that value. If the section with the found cell is collapsed, it expands it and visa versa.

Sub OpenSection()

Dim x As String
x = ActiveCell.Value

Dim y As String
y = Cells.Find(What:=(x), After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Address
'Range("b1").Value = y

With ActiveSheet
    With .Range(y).EntireRow
        If .ShowDetail = False Then
            .ShowDetail = True
        Else
            .ShowDetail = False
        End If
    End With
End With
End Sub