Note: The other languages of the website are Google-translated. Back to English

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


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:


The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Comments (24)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Is there a way to use the "Use checkbox to hide/unhide rows or columns with VBA code" on a protected worksheet?
This comment was minimized by the moderator on the site
@Wayne:

Private Sub CheckBox1_Click()
ActiveSheet.Unprotect Password:="xxxxx"
Rows("284:351").EntireRow.Hidden = Not CheckBox1
ActiveSheet.Protect Password:="xxxx"
End Sub
This comment was minimized by the moderator on the site
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?
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
Hi Lou,
Please make sure the checkboxes you created are ActiveX Controls checkboxes. These checkboxes works independently in your worksheet.
Thanks for your comment.
This comment was minimized by the moderator on the site
Hello,

Is there a way to hide only one row. If I put 6:9 all rows are hidden, but if I only put 6, it comes out as error. Would appreciate your help, thank you!
This comment was minimized by the moderator on the site
Hi,
Please change the second line in the code to:
[6:6].EntireRow.Hidden = Not CheckBox1.
Thank you for your comment.
This comment was minimized by the moderator on the site
I was try to unhide the first three role but it was not work.How can I do it ,I know it the person who send me that file wanna know my skill I can solve it or not.
This comment was minimized by the moderator on the site
Hi May,
Sorry I didn't get your point.
This comment was minimized by the moderator on the site
It worked, but then when I saved the file, and reopen it doesn't work. I tried saving it as a Macro excel sheet and as a regular workbook. What have I done wrong? I want to give it to someone else to use and don't want them to be confused.
This comment was minimized by the moderator on the site
Hi angela,After adding the code into workbook, please click File > Save as, choose where to save the file, in the Save As dialog box, choose Excel Macro-Enabled Workbook (*.xlsm) from the Save as type drop-down, and finally click Save. Then send this Excel Macro-Enabled workbook to others.
This comment was minimized by the moderator on the site
Very useful, but how can I use it to hide just a part of the spreadsheet? I mean for example cells B2:B11, how to combine those two functions? Thanks.
This comment was minimized by the moderator on the site
What if i wanted to use 3 different checkboxes and wanted them to each toggle hiding different groups of rows? I have tried and looked all over. When I try to modify this code the checkboxes seem to interfere with each other.
This comment was minimized by the moderator on the site
What if i wanted to use 3 different checkboxes and wanted them to each toggle hiding different groups of rows? I have tried and looked all over. When I try to modify this code the checkboxes seem to interfere with each other.
This comment was minimized by the moderator on the site
Esse código oculta, mas não executa a reexibição quando desmarcar.

Alguém pode complementar por favor? Preciso de um código para ocultar e reexibir colunas indicadas.

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

Obrigado
This comment was minimized by the moderator on the site
Hi Welington Mesquita,
This VBA code can acheive:
When the checkbox1 is checked, the column C:D are displayed;
When it is unchecked, the column C:D are hidden.
The code works well in my case. Which Excel version are you using?
This comment was minimized by the moderator on the site
Bom dia!
Espero que todos estejam bem!

Tenho uma situação onde preciso ocultar / reexibir a coluna utilizando o Control ActiveX quando solecionado o item.

Exemplo: Tenho coluna com os nomes: Planejado, Realizado, Análise Vertical, Análise Vertical, Variação (R$) e Variação (%), no entanto, que poder selecionar individual as colunas intercalando se necessário. Selecionar somente a coluna Realizado e Variação, mesmo não estando na sequencia.

Desde já agradeço,
Welington
This comment was minimized by the moderator on the site
Hi,
Sorry I don't understand what you mean. Would you mind providing a screenshot of your data?
You may need to attach a screenshot or a sample file to describe the problem you encountered more clearly.
This comment was minimized by the moderator on the site
E para ocultar colunas não sequências?
Exemplo: A e C

Esta situação seria aplicado de que forma para A e C ?

"Sub CheckBox1_Click privada ()
[C:D].EntireColumn.Hidden = Não CheckBox1
End Sub"
This comment was minimized by the moderator on the site
Hi,
The following VBA code can do you a favor. Please give it a try.
Private Sub CheckBox1_Click()
'Updated by Extendoffice 20220810
    [C:C, A:A].EntireColumn.Hidden = Not CheckBox1
End Sub
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations