Hide Unhide Columns not in a range

[knighttrader]

Hi

Using Excel on a Mac

I want to hide/unhide multiple columns that are not in a range using one button. e.g. Columns, A, E, S, T, Y

I want to hide/Unhide 45 columns in total. The following code works, but can anyone show me how to write the code better?

Many thanks

Sub HideUnhideLayColumns()

Columns("A").EntireColumn.Select
If Selection.EntireColumn.Hidden = False Then
Selection.EntireColumn.Hidden = True
Else
Selection.EntireColumn.Hidden = False
End If

Columns("E").EntireColumn.Select
If Selection.EntireColumn.Hidden = False Then
Selection.EntireColumn.Hidden = True
Else
Selection.EntireColumn.Hidden = False
End If

End Sub

 

[hajiali]

Try:

Columns("A,E,S,T,Y").EntireColumn.Hidden = True

 

[Fluff]

How about

Sub knighttrader()
   Dim Ary As Variant
   Dim i As Long
   
   Ary = Array("A", "E", "S", "T", "Y")
   For i = LBound(Ary) To UBound(Ary)
      Columns(Ary(i)).Hidden = Not Columns(Ary(i)).Hidden
   Next i
End Sub

 

[knighttrader]

Hi Fluff

Works a treat

Many thanks for the fast reply and help.

 

[Fluff]

Glad to help & thanks for the feedback.

 

[knighttrader]

Glad to help & thanks for the feedback.

Although your solution works I realise that it would be better if I could select the columns to be hidden based on a cell value rather specify the columns in the code.
The columns that I want to hide/unhide will change over time and I don't want to have to change the code every time.

This is the plan:
Range = A : LZ
I will type "HIDE' in ROW 3 for each column that needs to be hidden.
When I hit the button all columns within the range with HIDE in Row 3 should hide or unhide.

Can you help with that?

 

[Fluff]

Will there be anything else in row 3 other than the word Hide?

 

[knighttrader]

Will there be anything else in row 3 other than the word Hide?

No

 

[Fluff]

Ok, how about

Sub knighttrader()
   Range("A3:LZ3").SpecialCells(xlConstants).EntireColumn.Hidden = True
End Sub

 

[knighttrader]

Ok, how about

Sub knighttrader()
   Range("A3:LZ3").SpecialCells(xlConstants).EntireColumn.Hidden = True
End Sub

Thanks. That works as far as hiding columns, however, it doesn't unhide. Is it possible to unhide hidden columns using the same button as before?