Hide columns using controlX checkbox

[MPFraser7]

Hello,

I am trying to hide 150 columns using controlX checkbox but I can't seem to figure it out.

At first, I tried doing this:

Private Sub CheckBox3_Click()
If CheckBox3 = True Then
[E:F,I:M,t:Ai,am:ml].EntireColumn.Hidden = True
Else: [E:F,I:M,t:Ai,am:ml].EntireColumn.Hidden = False
End If
End Sub

This works fine but I am limited to 255 characters in a line so I can't input all 150 columns in a single line.

Then, I tried doing it using xAddress but I get Run-time error '13': Type mismatch

Private Sub Checkbox2_Click()
Dim xAddress As String
xAddress = Worksheets("Salary Determinate").[LZ3]
If CheckBox2 = True Then
Application.ActiveSheet.Columns(xAddress).Hidden = True
Else
Application.ActiveSheet.Columns(xAddress).Hidden = False
End If
End Sub

All of my columns are listed in cell LZ3 in this format: AB:AB,AD:AD, so on so forth.

The only way that I managed to get it working is by creating more than one checkbox in order to stay within the 255 character limit. In the following example, checkbox 5 is driven by checkbox 2. I know that this is not an efficient way to do it so I am looking for some help to get one of the first two working.

Private Sub CheckBox2_Click()
If CheckBox2.Value = True Then
CheckBox5.Value = True
Else
CheckBox5.Value = False
CheckBox5.Enabled = True
End If
If CheckBox2 = True Then
[AN:AN,AP:AP,AR:AR,AT:AT,AV:AV,AX:AX,AZ:AZ,BB:BB,BD:BD,BF:BF,BH:BH,BJ:BJ,BL:BL,BN:BN,BP:BP,BR:BR,BT:BT,BV:BV,BX:BX,BZ:BZ,CB:CB,CD:CD,CF:CF,CH:CH,CJ:CJ,CL:CL,CN:CN,CP:CP,CR:CR,CT:CT,CV:CV,CX:CX,CZ:CZ,DB:DB,DD:DD,DF:DF,DH:DH,DJ:DJ,DL:DL,DN:DN,DP:DP,DR:DR].EntireColumn.Hidden = True
Else: [AN:AN,AP:AP,AR:AR,AT:AT,AV:AV,AX:AX,AZ:AZ,BB:BB,BD:BD,BF:BF,BH:BH,BJ:BJ,BL:BL,BN:BN,BP:BP,BR:BR,BT:BT,BV:BV,BX:BX,BZ:BZ,CB:CB,CD:CD,CF:CF,CH:CH,CJ:CJ,CL:CL,CN:CN,CP:CP,CR:CR,CT:CT,CV:CV,CX:CX,CZ:CZ,DB:DB,DD:DD,DF:DF,DH:DH,DJ:DJ,DL:DL,DN:DN,DP:DP,DR:DR].EntireColumn.Hidden = False
End If
End Sub

Private Sub CheckBox5_Click()
If CheckBox5 = True Then
[DT:DT,DV:DV,DX:DX,DZ:DZ,EB:EB,ED:ED,EF:EF,EH:EH,EJ:EJ,EL:EL,EN:EN,EP:EP,ER:ER,ET:ET,EV:EV,EX:EX,EZ:EZ,FB:FB,FD:FD,FF:FF,FH:FH,FJ:FJ,FL:FL,FN:FN,FP:FP,FR:FR,FT:FT,FV:FV,FX:FX,FZ:FZ,GB:GB,GD:GD,GF:GF,GH:GH,GJ:GJ,GL:GL,GN:GN,GP:GP,GR:GR,GT:GT,GV:GV,GX:GX].EntireColumn.Hidden = True
Else: [DT:DT,DV:DV,DX:DX,DZ:DZ,EB:EB,ED:ED,EF:EF,EH:EH,EJ:EJ,EL:EL,EN:EN,EP:EP,ER:ER,ET:ET,EV:EV,EX:EX,EZ:EZ,FB:FB,FD:FD,FF:FF,FH:FH,FJ:FJ,FL:FL,FN:FN,FP:FP,FR:FR,FT:FT,FV:FV,FX:FX,FZ:FZ,GB:GB,GD:GD,GF:GF,GH:GH,GJ:GJ,GL:GL,GN:GN,GP:GP,GR:GR,GT:GT,GV:GV,GX:GX].EntireColumn.Hidden = False
End If
End Sub

 

[CalcSux78]

UNION will allow you to blend multiple complex ranges together into one non-sequential range. For your scenario, looks like you are changing visibility to even numbered columns between 40 and 206. This would be less messy:

Private Sub Checkbox2_Click()
Dim c%


Application.ScreenUpdating = False
For c = 40 To 206 Step 2
    ActiveSheet.Columns(c).Hidden = CheckBox2.Value
Next c
Application.ScreenUpdating = True


End Sub

 

[MPFraser7]

This works, thanks! I can now hide every second column from the 40th column to the 348th column. Now I am trying to hide every second column from the 352nd column to 664th column. I tried using the same code for Checkbox4 but no luck, I tried changing the variable to different letter, no luck. Is it possible or does it have to be within the same check box?

 

[CalcSux78]

The highlights show what should change between the two subs:

Private Sub CheckBox[B][COLOR=#ff0000]4[/COLOR][/B]_Click()
Dim c%




Application.ScreenUpdating = False
For c = [COLOR=#ff0000][B]352 [/B][/COLOR]To [B][COLOR=#ff0000]664 [/COLOR][/B]Step 2
    ActiveSheet.Columns(c).Hidden = CheckBox[COLOR=#ff0000][B]4[/B][/COLOR].Value
Next c
Application.ScreenUpdating = True




End Sub