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.
Second attempt to add more logic (Does not work and could be very wrong)
Any help is greatly appreciated
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