VBA to dynamic toggle columns hide/unhide

Contributor

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?

16 Replies

@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
Hi Hans,

This works, but I have multiple columns with M1, and now it hides only the first column. Do you know why?

@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

 

You are amazing Hans!

You also happen to know the solution to open and close anything except M1?
Something with <> * -M1 I suppose?

@Celia9 

Do you want the toggle button to toggle the visibility of all columns based on whether their header ends in "- M1"? If so, try this:

Private Sub ToggleButton1_Click()
    Const HeaderRow = 1
    Dim rng As Range
    Dim adr As String
    Cells.EntireColumn.Hidden = Not ToggleButton1.Value
    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
I have 5 groups M1, M2, M3, M4 and M5
I would like to hide all, except M1.

Is that what you wanted this code to do? I get runtime error 1004
Application-defined or object-defined error

@Celia9 

The old code toggles the visibility of columns with M1 on and off.

What exactly should the new code do? Hide M2 - M5 and show M1? Or toggle - if so, what?

Yes, hide M2 - M5 and show M1

@Celia9 

Perhaps this?

Private Sub ShowOnlyM1()
    Const HeaderRow = 1
    Dim rng As Range
    Dim adr As String
    Cells.EntireColumn.Hidden = True
    Set rng = Rows(HeaderRow).Find(What:="* - M1", LookAt:=xlWhole)
    If Not rng Is Nothing Then
        adr = rng.Address
        Do
            rng.EntireColumn.Hidden = False
            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
    Range("A1").Select
End Sub
Thank you for helping me, Hans!

I get: Unable to set the hidden property of the Range class.

This row is yellow
Cells.EntireColumn.Hidden = True

I tried to change
Range("A1").Select
To A4, because that is where my table begins (I didn't know if that would matter)
But it doesn't obviously

@Celia9 

The code works for me. Could you attach a sample workbook (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Thanks in advance.

@Celia9 

Thanks, that helped. See the attached version. I added 5 command buttons.

This is amazing!!!

I cleaned up your version, I also have M6, so when I filter, I do not see the last 3 columns anymore (workorder, description ann location)

Where in the code can I find this option?

And also, how can I thank you for all your help? Is there something I can sponsor or something like that?

@Celia9 

The line

 

    Range("C1").Resize(1, LastColumn - 5).EntireColumn.Hidden = True

 

hides all columns in the used range except for the first two and the last three. In the sample workbook, the data extend from column A to column R, so columns C to O get hidden, while columns A, B, P, Q and R will always remain visible.

Thank you so much!