VBA Code to Hide/Unhide Columns across multiple Sheets while keeping columns grouped

FinanceAnalyst

New Member
Joined
May 22, 2019
Messages
1
Hello,

I am new to VBA coding and would appreciate assistance with the following scenario. I have a file that I would like to hide a range of columns across multiple sheets. I am wanting one button to be able to do all this at once if possible.

On sheet 1, I am looking to hide columns (L:R) if unhidden and to un-hide that same set while keeping them grouped. It may be easier to hide only the columns that are currently showing (L, N, P, and R). I don't want to refer to sheet names as I am consistently changing this file by adding and or removing sheets.

On Sheet 2- Continuous, I want to do the same. However I want to hide columns (M:T) and then unhide if hidden while still keeping them grouped. The columns currently showing are (M, O, Q, S, and T).

I've tried several codes and haven't had any luck the only one that seems to work is one that hides/unhides a specific range of columns across ALL sheets (even then it won't keep my columns grouped.

Code:
Sub Button5_Click()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
            ws.Columns("L:T").EntireColumn.Hidden = Not ws.Columns("L:T").Hidden
    Next ws
End Sub

Second attempt to add more logic (Does not work and could be very wrong)
Code:
Sub Button1_Click()'Hides/unhides Specified columns On Specified Sheet


If Activesheet.Columns("L,N,P,R").EntireColumn.Hidden = True Then


Activesheet.Columns("L,N,P,R").EntireColumn.Hidden = False 'Unhides Selected Colunms




'Hides/unhides columns On All Sheets Except The Ones Specified Below
Dim x As Long
  
  For x = 2 To Worksheets.Count
    With Sheets(x)
        .Range("M,O,Q,S,T").EntireColumn.Hidden = Not .Range("M,O,Q,S,T").Hidden 'Hides Selected columns
        'End of Code To Be Looped
    End With
            Next x
End Sub

Any help is greatly appreciated :)
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Forum statistics

Threads
1,224,918
Messages
6,181,735
Members
453,064
Latest member
robatthe2A

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top