+ Reply to Thread
Results 1 to 6 of 6

Button to toggle Hide/Show multiple columns

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    09-23-2015
    Location
    Toronto, Ontario
    MS-Off Ver
    Excel 2010
    Posts
    187

    Button to toggle Hide/Show multiple columns

    Hi I am new to VBa and trying to have a button that toggles hide/show certain columns at once.

    The columns I would like otthide are AL, BD-BE-BF-BG

    Sub FV_Click()
    
      If Worksheets("Overview").Range("AL:AL").EntireRow.Hidden = True Then
              Worksheets("Overview").Range("AL:AL").EntireRow.Hidden = False
        Else
           Worksheets("Overview").Range("AL:AL").EntireRow.Hidden = True
        End If
        
    End Sub
    I did this to hide AL as a start, it works but when I paste that 4 more times replacing AL for BD, then BE, etc.. the macro implodes my spreadsheet. Clearly this isn't efficient or proper way

    Can anyone suggest me a code to Hide/Show those columns upon click of a button.

    Thanks in advance!

  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Button to toggle Hide/Show multiple columns

    I think that you need to use EntireColumn rather than EntireRow

  3. #3
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Button to toggle Hide/Show multiple columns

    You also might prefer syntax like
    With Worksheets("Overview").Range("AL:AL").EntireColumn
        .Hidden = Not(.Hidden)
    End With
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  4. #4
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,888

    Re: Button to toggle Hide/Show multiple columns

    Or, to do all the columns
    Sub FV_Click()
    With Worksheets("Overview").Range("AL:AL,BD:BG").EntireColumn
       .Hidden = Not .Hidden
    End With
    End Sub

  5. #5
    Forum Contributor
    Join Date
    09-23-2015
    Location
    Toronto, Ontario
    MS-Off Ver
    Excel 2010
    Posts
    187

    Re: Button to toggle Hide/Show multiple columns

    Wasn't familiar witht he the With statement, this works great thank you very much everyone

  6. #6
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,888

    Re: Button to toggle Hide/Show multiple columns

    Glad we could help & thanks for the feedback

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] How to hide and show userform and werksheet with Toggle Nutton and Command Button
    By DawidV in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 02-26-2017, 07:22 AM
  2. [SOLVED] Toggle Button -Show Hide Column Range Fails
    By sl729 in forum Excel General
    Replies: 5
    Last Post: 05-22-2016, 09:17 PM
  3. Use a Shape as a Toggle Button to Show and Hide a Worksheet
    By HangMan in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-19-2015, 09:49 AM
  4. Show/Hide Rows & Columns Toggle Button
    By axegrynder in forum Excel General
    Replies: 2
    Last Post: 06-29-2015, 06:38 PM
  5. Hide/Unhide Multiple Sets of Columns with Toggle Button
    By Mischief433 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-06-2012, 05:55 AM
  6. [SOLVED] Hide Show Excel Toolbars using a Toggle Button
    By HangMan in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 07-11-2012, 05:11 PM
  7. VBA hide row based on pull down with button to toggle show/hide
    By myronr in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-12-2012, 06:07 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1