CODEWITHSUNDEEP
excelvbahideshow
AntonioN
AntonioN

Reputation: 13

Hide Different Columns

I am trying to hide different columns that are not next to each other.

I have a the below code but this allows me to only hide a range, eg in this case columns H:K. If I want to hide additional columns, say I want to hide H:K but also S:T, X:Y and column Z:Z - how do I get the below code to work for non-adjacent columns?

Option Explicit

'---------->>
Public Sub ToggleColumns()
    Dim SH As Worksheet
    Dim Rng As Range
    Dim obj As Variant
    Dim BTN As Button
    Dim iLen As Long
    Const myColumns As String = "H:K"               '<<===== Change
    Const sFees As String = "Fees"                       '<<===== Change
    Const sHidden As String = " Hidden"
    Const sVisible As String = " Visible"

    Set SH = ActiveSheet
    Set BTN = SH.Buttons(Application.Caller)
    Set Rng = SH.Columns(myColumns)
    With Rng.EntireColumn
        .Hidden = Not .Hidden
        If .Hidden Then
        iLen = Len(sHidden) + Len(sFees)
            BTN.Characters.Text = sFees & " Hidden"
            With BTN.Characters(Start:=1, Length:=iLen).Font
                .Name = "Arial"
                .FontStyle = "Bold"
                .Size = 10
                .ColorIndex = 3  '\\ RED
            End With
        Else
        iLen = Len(sVisible) + Len(sFees)
        BTN.Characters.Text = sFees & " Visible"
            With BTN.Characters(Start:=1, Length:=iLen).Font
                .Name = "Arial"
                .FontStyle = "Bold"
                .Size = 10
                .ColorIndex = 4  '\\ GREEN
            End With
        End If
    End With

End Sub

Upvotes: 1

Views: 84

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57693

Replace

Const myColumns As String = "H:K"

with

Const myColumns As String = "H:K,S:T,X:Y,Z:Z"

and

Set Rng = SH.Columns(myColumns)

with

Set Rng = SH.Range(myColumns)

because Columns() does not support addresses with discontinued columns.

Upvotes: 1

Related Questions