Toggle button to hide columns based on test in row 1

[Fantrasp12]

Hi. I went through old post already and have failed to make this successful. So posting new one here.

What i want to do:
I have a header in row 1, and instead of assigning static columns tot be hidden with toggle (because it is so many columns and i seem to run into issues beyond certain number of columns in range but also if range changes then i have to redo all of it) i would like for it to search the top row (row 1) for the word "performance" across column range BB:TX and show/hide entire column based on result. Can you please advise what i would modify here? thank you

 

[HaHoBe]

Hi Fantrasp12,

Can you please advise what i would modify here?

Maybe add a ToggleButton from the ActiveX-Controls, add the following code by double-clicking on the control overwriting the inserted bit for this control by:

Private Sub ToggleButton1_Click()
' https://www.mrexcel.com/board/threads/toggle-button-to-hide-columns-based-on-test-in-row-1.1226974/
Dim rngCell As Range
Dim blnHide As Boolean

With ToggleButton1
  If .Caption <> "Unhide" Then
    .Caption = "Unhide"
    blnHide = True
  Else
    .Caption = "Hide"
    blnHide = False
  End If
End With

Application.ScreenUpdating = False
For Each rngCell In Range("BB1:TX1")
  With rngCell
    If InStr(LCase(.Value), "performance") > 0 Then
      .Columns.Hidden = blnHide
    End If
  End With
Next rngCell
Application.ScreenUpdating = True

End Sub

Return to Excel and the sheet, make sure to end Debug-Mode. Any click should alter the Caption of the button and perform either hiding or unhiding.

Ciao,
Holger