Automatically Unhide Columns on GoTo

[Silverjman]

I have a sheet1 with sets of grouped (hidden with + box or visible with the - box) columns.

Each group of hidden columns is a Named Range.

On sheet2 I have cells/formulas linking to the grouped/hidden columns/cells on Sheet1.

When I jump (doubleclick or Ctrl-G) from Sheet2 to a specific cell/column on Sheet1 I would like to automatically tigger the appropriate Named Range to Unhide. Right now the code below only unhides the column I've jumped to e.g. D:D when I would like it to unhide the entire Named Range that D:D falls into which is $B:$X.

Private Sub Worksheet_Activate()


Dim nm As Name, rng As Range
For Each nm In ThisWorkbook.Names
    Set rng = Intersect(ActiveCell, Range(nm.RefersToRange.Address))
    
    If rng.EntireColumn.Hidden = True Then
       rng.EntireColumn.Hidden = False
End If

    If Not rng Is Nothing Then
       
        Exit For
    End If
Next nm

End Sub

 

[Silverjman]

Does it work if you don't use a hyperlink and instead do Ctrl-G and paste in Sheet!$D10 and hit enter?

 

[Silverjman]

Does your code work for you if you hit Ctrl-G and copy and paste "Sheet!D10" and then click enter?

 

[MarcelBeug]

Yes, it does, provided I start on a different sheet.

 

[WinteE]

Silverjman
You have probably different cells on Sheet 2 with a Range-name as a value.
Are tese cells stored in a table ?

 

[WinteE]

Copy this to the VBA part of Sheet2 :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    Worksheets("Sheet1").Range("A1:D1").EntireColumn.Hidden = True 'hide all columns on Sheet1 here
    
    If Not Intersect(Target, Range("A1:A10")) Is Nothing Then 'change the range to were the cells with names are
        For Each nm In ThisWorkbook.Names
            If nm = Target.Value Then
                Range(nm).EntireColumn.Hidden = False
            End If
        Next nm
    End If
    
End Sub