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

 

[WinteE]

Range("Test").EntireColumn.Hidden = True

... works fine for me

 

[MarcelBeug]

Another suggestion from Flevoland (Province in the Netherlands):

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 Not rng Is Nothing Then
       Range(nm.RefersToRange.Address).EntireColumn.Hidden = False
       Exit For
    End If
Next nm

End Sub

 

[WinteE]

Kleine wereld ....

 

[Silverjman]

Thank you both; I don't think I understand how my code is working you WinteE and when I use your code Marcel it unhides all the grouping on the sheet not just the one that applies to the Named Range of the cell I jumped into.

 

[WinteE]

In Marcels code replace Range(nm.RefersToRange.Address) with Range(rng)

 

[Silverjman]

In Marcels code replace Range(nm.RefersToRange.Address) with Range(rng)

Weird now it is giving me a Debug at the line Range(rng).EntireColumn.Hidden = False

Wish I wasn't so simple...

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

End Sub

 

[Silverjman]

whoops this is Marcels code plus your addition, my LAST paste had one too many NOTs

Code:
Dim nm As Name, rng As Range
For Each nm In ThisWorkbook.Names
    Set rng = Intersect(ActiveCell, Range(nm.RefersToRange.Address))
    
     If Not rng Is Nothing Then
       Range(rng).EntireColumn.Hidden = False
       Exit For
    End If
Next nm

End Sub

This only unhides one column

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 Not rng Is Nothing Then
       rng.EntireColumn.Hidden = False
       Exit For
    End If
Next nm

End Sub

 

[MarcelBeug]

This is a reaction to post #5. I didn't see the other posts yet.

There must be some misunderstanding.
I tested with a hyperlink on sheet2 linking to a cell on sheet1 that is part of a named range.
When I first hide columns on Sheet1, than follow the hyperlink, only the columns that are part of the named range become visible.
I tried with multiple named ranges, but only the named range to where I'm jumping becomes visible.
Possibly you have overlapping named ranges or so?

Otherwise please provide more details on what's going wrong.

 

[Silverjman]

Thanks for all the replies, hmm no I don't have any overlapping ranges. I'm trying to think of another way to describe it or somehow do a Debug.Print