Hide/un-hide columns via VBA

A command button on my spreadsheet should hide/un-hide specified columns (in VBA code).    Please see attached XLS with VBA code (currently not working) and specific details as to what I would like to accomplish.

I'd welcome your feedback.   Thank you!

EEH
Hide-Unhide-Columns.xlsm
ExpExchHelpAnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Fabrice LambertFabrice LambertCommented:
Hi,

The EntireColumn property represent a single column, so calling it on a range spanning over multiple columns make no sens.

Try the following:
Option Explicit

Private Sub CommandButton1_Click()
    Dim wb As Excel.Workbook
    Set wb = ThisWorkbook
    
    Dim ws As Excel.Worksheet
    Set ws = wb.Worksheets("Hide-Unhide")
    If CommandButton1.Caption = "Hide Information" Then
        hideUnhideColumns ws.Range("D:D, F:H"), True
        CommandButton1.Caption = "Show Information"
    Else
        hideUnhideColumns ws.Range("D:D, F:H"), False
        CommandButton1.Caption = "Hide Information"
    End If
    Set ws = Nothing
    Set wb = Nothing
End Sub

Private Sub hideUnhideColumns(ByRef cols As Excel.Range, ByVal hidden As Boolean)
    Dim col As Excel.Range
    For Each col In cols.columns
        col.hidden = hidden
    Next
End Sub

Open in new window

Additional note:
You should give a meaningfull name to your command button.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Roy CoxGroup Finance ManagerCommented:
Consider using Custom Views

Create, apply, or delete a custom view

The this code will hide or unhide the columns. I have created two custom views one called FullView and the other called HiddenView.

Please note that  If you only have one Table in your entire Workbook, you cannot use Custom Views. The command is inactive.

Private Sub CommandButton1_Click()
    With ThisWorkbook
        If CommandButton1.Caption = "Hide Information" Then
            .CustomViews("HiddenView").Show
            CommandButton1.Caption = "Show Information"
        Else
            .CustomViews("FullView").Show
            CommandButton1.Caption = "Hide Information"
        End If
    End With
End Sub

Open in new window


Please note that Custom Views cannot be used if the workbook contains Tables
Hide-Unhide-Columns.xlsm
0
Roy CoxGroup Finance ManagerCommented:
Alternative suggestion

Private Sub CommandButton1_Click()
   Dim rRng As Range

    Set rRng = Me.Range("A:C,E:E,I:J").EntireColumn
    rRng.Hidden = Not rRng.Hidden
   
    Me.CommandButton1.Caption = Choose(CInt(rRng.Hidden) + 2, "Show Data", "Hide Data")
End Sub

Open in new window

Hide-Unhide-Columns.xlsm
0
ExpExchHelpAnalystAuthor Commented:
Thank you... both solutions work great.   Appreciate your assistance.
0
Roy CoxGroup Finance ManagerCommented:
Pleased to help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.