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
ExpExchHelpAsked:
Who is Participating?
 
Fabrice LambertConnect With a Mentor Fabrice 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
 
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 CoxConnect With a Mentor Group 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
 
ExpExchHelpAuthor Commented:
Thank you... both solutions work great.   Appreciate your assistance.
0
 
Roy CoxGroup Finance ManagerCommented:
Pleased to help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.