Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to use checkbox to hide/unhide rows or columns in Excel?

Supposing you need to use a checkbox to hide or unhide certain rows or columns. For example, when the Active X Control checkbox is checked, the certain rows or columns are displayed, otherwise, they will be hidden. This article shows you the method of how to use checkbox to hide/unhide rows or columns in Excel with details.

Use checkbox to hide/unhide rows or columns with VBA code


You may interest in:

Easily insert or delete multiple check boxes/option buttons in bulk in Excel

With the Kutools for Excel's Batch Insert Check Boxes/Option Buttons or Batch Delete Check Boxes/Option Buttons utilities, you can insert or delete multiple check boxes or option buttons in bulk in your Excel worksheet. See below screenshot:

Kutools for Excel includes more than 300 handy Excel tools. Free to try with no limitation in 60 days. Read More      Download the free trial now


Use checkbox to hide/unhide rows or columns with VBA code


The following VBA code can help you hide/unhide certain rows or columns with checkbox.

1. After inserting an Active X Control checkbox to the worksheet, right click on the checkbox and then select View Code from the right-clicking menu. See screenshot:

2. In the Microsoft Visual Basic for Applications window, copy and paste the following VBA code into the Code window.

VBA code: Use checkbox to hide/unhide rows or columns

Private Sub CheckBox1_Click()
    [C:D].EntireColumn.Hidden = Not CheckBox1
End Sub

Notes:

1. In the VBA code, [C:D]. EntireColumn means that the column C and D will be hide or unhide by checking or unchecking the corresponding checkbox.

2. For hiding or displaying certain rows such as row 6:9, please change the [C:D]. EntireColumn to [6:9]. EntireRow in the VBA code.

3. Press the Alt + Q keys simultaneously to close the Microsoft Visual Basic for Applications window.

4. Now please turn off the Design Mode under the Developer tab as below screenshot shown:

From now on, when the checkbox is checked, the specified rows or columns are displayed. When it is unchecked, the specified rows or columns are hidden.


Related articles:


Recommended Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 71%, and Help You To Stand Out From Crowd!

Would you like to complete your daily work quickly and perfectly? Kutools For Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for 1500+ work scenarios, helps you solve 82% Excel problems.

  •  Deal with all complicated tasks in seconds, help to enhance your work ability, get success from the fierce competition, and never worry about being fired.
  •  Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  •  Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands, and give you a healthy body.
  •  Become an Excel expert in 3 minutes, and get admiring glance from your colleagues or friends.
  •  No longer need to remember any painful formulas and VBA codes, have a relaxing and pleasant mind, give you a thrill you've never had before.
  •  Spend only $39, but worth than $4000 training of others. Being used by 110,000 elites and 300+ well-known companies.
  •  60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  •  Change the way you work now, and give you a better life immediately!

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • Help you work faster and easily stand out from the crowd! One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Lou · 3 days ago
    Hi,

    Hi, I've done the code, it's work good but when I try to make the same in more than one checkbox at the same sheet they work 'together', only hide or unhide the rows if all three checkboxes are selected.
    I am tryint to make these three checkboxes works independent one from another. And let the users mark only one box for at time.
  • To post as a guest, your comment is unpublished.
    Ed · 3 months ago
    Hello,

    I am trying to use this code to hide/unhide on a separate sheet from the checkbox. What do I have to add to make this work? Thanks in advance.

    Private Sub CheckBox1_Click()
    [C:D].EntireColumn.Hidden = Not CheckBox1
    End Sub
    • To post as a guest, your comment is unpublished.
      crystal · 2 months ago
      Good day,
      As the following code shown, please replace the "Sheet4" with your separate sheet name.

      Private Sub CheckBox1_Click()
      [Sheet4!C:D].EntireColumn.Hidden = Not CheckBox1
      End Sub
  • To post as a guest, your comment is unpublished.
    Cole · 1 years ago
    It seems like this code will only go one direction, i.e. only hide and not unhide them when clicked. Is there a way to correct this?
    • To post as a guest, your comment is unpublished.
      Kent Thegreet · 7 months ago
      We have same problem but find a way to make it work. Please use ActiveX Controls checkbox and not at the Form Control checkbox. The code perfectly works with ActiveX checkbox.


      Private Sub CheckBox1_Click()
      [C:D].EntireColumn.Hidden = Not CheckBox1
      End Sub
    • To post as a guest, your comment is unpublished.
      Kentthegreet · 7 months ago
      We have same problem but I found out we're using Form Controls and not ActiveX Controls. The code perfectly works in ActiveX Control Checkbox button. Just turn off the Design Mode so you can click check the Checkbox.
  • To post as a guest, your comment is unpublished.
    Pieter Ramaut · 1 years ago
    @Wayne:

    Private Sub CheckBox1_Click()
    ActiveSheet.Unprotect Password:="xxxxx"
    Rows("284:351").EntireRow.Hidden = Not CheckBox1
    ActiveSheet.Protect Password:="xxxx"
    End Sub
  • To post as a guest, your comment is unpublished.
    Wayne Bailey · 1 years ago
    Is there a way to use the "Use checkbox to hide/unhide rows or columns with VBA code" on a protected worksheet?