Hide/Unhide Rows with a Checkbox

webdoc

New Member
Joined
Oct 8, 2002
Messages
18
Can someone tell me how to unhide rows when a checkbox is checked and then hide the same rows when it is unchecked? I'm new to Excel programming, so feel free to give lots of detail if you like. Thanks!!
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
Say the rows you want to hide and unhide are 5:10. Put a macro like this in a module

Sub HideUnhide()

Rows("5:10").Select
If Selection.EntireRow.Hidden = False Then
Selection.EntireRow.Hidden = True
Else
Selection.EntireRow.Hidden = False
End If
Range("$A$1").Select

End Sub

Assign this macro to your CheckBox
 

NateO

Legend
Joined
Feb 17, 2002
Messages
9,700
Howdy WebDoc, welcome to the board.

If you're using an activex checkbox, in design mode, right click on the check box, and click view code.

In the now opened worksheet module, paste the following procedure:

<pre>
Private Sub CheckBox1_Click()
If CheckBox1 = True Then
[5:10].EntireRow.Hidden = False
Else: [5:10].EntireRow.Hidden = True
End If
End Sub</pre>

You'll want to change Checkbox1 to the name of your checkbox.

Hope this helps.
 

dogpile

New Member
Joined
Oct 24, 2005
Messages
21

ADVERTISEMENT

Hope it's alright if I jump in on this with another question:

Could you show me the code for the check box if I wanted to unhide the rows where ([a5:a10] = 1) and keeping other rows hidden?
 

OfficeMan81

New Member
Joined
Aug 10, 2011
Messages
1
In my first column I have a brand name (say Coca-cola) and I only want to show rows with Coca-Cola when I check the box.

The brands are populated from a list on a separate sheet. 10 brands.

Can this be done?

Thanks
 

Briantoo

New Member
Joined
Aug 11, 2011
Messages
1

ADVERTISEMENT

Wow...this was a HUGE help for me--thanks!
 

Pinkie15

New Member
Joined
Nov 30, 2015
Messages
1
Please excuse the thread revival, but my google search brought me to this thread.

Just to say thanks to lenze and NateO, a simply given answer that's enabled me to input VBA code (first for me) and it works !! It's for a word document I'm re-designing into Excel (why do people write tables in word ???).
 

mnmio15

New Member
Joined
Mar 17, 2016
Messages
1
Say the rows you want to hide and unhide are 5:10. Put a macro like this in a module

Sub HideUnhide()

Rows("5:10").Select
If Selection.EntireRow.Hidden = False Then
Selection.EntireRow.Hidden = True
Else
Selection.EntireRow.Hidden = False
End If
Range("$A$1").Select

End Sub

Assign this macro to your CheckBox


Hello,
If I put in this Macro, but then need to add some rows above those covered by the checkbox functionality, the Macro won't update automatically will it?
e.g. rows 10-20 are covered by the checkbox tick. If I then need to add a new row at line 6, the macro code still refers to rows 10-20 whereas I would like it to automatically realise that now the checkbox tick is covering rows 11-21.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,964
Messages
5,599,069
Members
414,281
Latest member
Engjamal2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top