Forum Discussion

Celia9's avatar
Celia9
Brass Contributor
Jul 07, 2022

VBA to dynamic toggle columns hide/unhide

Hi,

 

I have a VBA to toggle my columns hide/unhide

Private Sub ToggleButton1_Click()


Dim xAddress As String
xAddress = "F:G"
If ToggleButton1.Value Then
Application.ActiveSheet.Columns(xAddress).Hidden = True
Else
Application.ActiveSheet.Columns(xAddress).Hidden = False
End If
End Sub

 

But instead of F:G I want to select the columns based on a part of a name of a header in my table.

 

For example I have a table with these header names;

person1 - M1

person2 - M1

person3 - M2

 

I want to hide/unhide columns based upon the - M1 part of the headername.

 

Anyone knows a solution?

excel
Macros and VBA

16 Replies

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jul 07, 2022

    Celia9 

    For example

    Private Sub ToggleButton1_Click()
    Const HeaderRow = 1
    Dim rng As Range
    Set rng = Rows(HeaderRow).Find(What:="* - M1", LookAt:=xlWhole)
    If rng Is Nothing Then
        MsgBox "M1 not found!", vbExclamation
        Exit Sub
    End If
    rng.EntireColumn.Hidden = ToggleButton1.Value
End Sub
    • Celia9's avatar
      Celia9
      Brass Contributor
      Jul 08, 2022
      Hi Hans,

      This works, but I have multiple columns with M1, and now it hides only the first column. Do you know why?
      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Jul 08, 2022

        Celia9 

        The code that I posted looks only for the first instance. Here is a version that should handle all instances:

        Private Sub ToggleButton1_Click()
    Const HeaderRow = 1
    Dim rng As Range
    Dim adr As String
    Set rng = Rows(HeaderRow).Find(What:="* - M1", LookAt:=xlWhole)
    If Not rng Is Nothing Then
        adr = rng.Address
        Do
            rng.EntireColumn.Hidden = ToggleButton1.Value
            Set rng = Rows(HeaderRow).Find(What:="* - M1", After:=rng, LookAt:=xlWhole)
            If rng Is Nothing Then Exit Do
        Loop Until rng.Address = adr
    End If
End Sub

         

Resources