Hide/Unhide Multiple columns

[johannes2008]

Hello

I am trying to create a button that hides/unhides multiple columns that are not adjacent (ie col A, X, AB,BX). My plan is to assign all the columns to a variable and then use an if/else statement for hide/unhide but I don't know how to assign multiple ranges to a variable. Is my plan workable, if not what do you suggest?

Thanks

 

[Z51]

Here's how to hide multiple, non-adjacent columns:

Dim rng As Range
Set rng = Range("A:B,F:G,X:X,Z:AB")
rng.EntireColumn.Hidden = True

or simply this, without using a variable:

Range("A:B,F:G,X:X,Z:AB").EntireColumn.Hidden = True

 

[johnnyL]

To hide:

Range("A:A, X:X, AB:AB ,BX:BX").EntireColumn.Hidden = True

Set to False to unhide.

 

[johannes2008]

Here's how to hide multiple, non-adjacent columns:

Dim rng As Range
Set rng = Range("A:B,F:G,X:X,Z:AB")
rng.EntireColumn.Hidden = True

or simply this, without using a variable:

Range("A:B,F:G,X:X,Z:AB").EntireColumn.Hidden = True

This is the code i am trying to use

Private Sub CommandButton1_Click()

Dim rng As Range
Set rng = Range("AC:AD,AI,AK:AL,AQ,AS:AT")

If rng.EntireColumn.Hidden = False Then
    rng.EntireColumn.Hidden = True
    Else
        rng.EntireColumn.Hidden = False
End If
End Sub

but I get an error.

 

[Z51]

First, you reference AI and AQ alone, they should be AI:AI and AQ:AQ. Second, in your example you don't need to test if the columns are hidden if you're just going to reverse their state, you can just reverse their state using "Not", e.g.:

Range("AC:AD,AI:AI,AK:AL,AQ:AQ,AS:AT").EntireColumn.Hidden = Not Range("AC:AD,AI:AI,AK:AL,AQ:AQ,AS:AT").EntireColumn.Hidden

Running this macro multiple times will simply hide/unhide the set of columns each time.

 

[johnnyL]

       Private Sub CommandButton1_Click()

Dim rng As Range
Set rng = Range("AC:AD,AI:AI,AK:AL,AQ:AQ,AS:AT")

If rng.EntireColumn.Hidden = False Then
rng.EntireColumn.Hidden = True
Else
rng.EntireColumn.Hidden = False
End If
End Sub

 

[johannes2008]

First, you reference AI and AQ alone, they should be AI:AI and AQ:AQ. Second, in your example you don't need to test if the columns are hidden if you're just going to reverse their state, you can just reverse their state using "Not", e.g.:

Range("AC:AD,AI:AI,AK:AL,AQ:AQ,AS:AT").EntireColumn.Hidden = Not Range("AC:AD,AI:AI,AK:AL,AQ:AQ,AS:AT").EntireColumn.Hidden

Running this macro multiple times will simply hide/unhide the set of columns each time.

Oh right. Fixed that and used your "Not" code and it worked perfectly. Thank you very much!