I need a button to hide/unhide rows based on a cell content

mauricelopes

New Member
Joined
Nov 30, 2009
Messages
9
I have a shopping list i use for my restaurant EVERYDAY i have 2 cells (Unit and Case) on each row, i want a button on top of the sheet that i can toggle hide the rows that dont have anything in either those 2 cells in each row.

<TABLE style="WIDTH: 822pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=1095 x:str><COLGROUP><COL style="WIDTH: 295pt; mso-width-source: userset; mso-width-alt: 14372" width=393><COL style="WIDTH: 314pt; mso-width-source: userset; mso-width-alt: 15323" width=419><COL style="WIDTH: 115pt; mso-width-source: userset; mso-width-alt: 5595" width=153><COL style="WIDTH: 98pt; mso-width-source: userset; mso-width-alt: 4754" width=130><TBODY><TR style="HEIGHT: 55.5pt; mso-height-source: userset" height=74><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 295pt; HEIGHT: 72.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71 height=97 rowSpan=2 width=393>Description</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 314pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71 rowSpan=2 width=419>Description/Manufactuer/Brand</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #010000; WIDTH: 213pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72 width=283 colSpan=2> ORDER</TD></TR><TR style="HEIGHT: 17.25pt; mso-height-source: userset" height=23><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; HEIGHT: 17.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 height=23> Unit</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69> Case</TD></TR><TR style="HEIGHT: 18pt" height=24><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 295pt; HEIGHT: 18pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=24 width=393 x:str="Tomatoes ">Tomatoes </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 314pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=419> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 115pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=153> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 98pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 width=130> </TD></TR><TR style="HEIGHT: 18pt" height=24><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 295pt; HEIGHT: 18pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=24 width=393>Lettuce (Regular)</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 314pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=419></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 115pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=153> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 98pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 width=130> </TD></TR><TR style="HEIGHT: 18pt" height=24><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 295pt; HEIGHT: 18pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=24 width=393>Lettuce (Romain Hearts)</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 314pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=419> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 115pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 width=153> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 98pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 width=130> </TD></TR></TBODY></TABLE>

My cell range for "Unit" is F15:F197 and Cell range for "Case" is G15:G197, this is where i put in my order quantaties, i dont want to fax my vendor a whole HUGE list with items im not ordering, i just want to show the rows where something in any of those two cells.

i never used macros before so please be patient and if you can explain step by step.

thanks,
Maurice
 
Last edited:

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Domski

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
You could maybe use Autofilter to achieve this without messing about with VBA.

Dom
 

Steeviee

Active Member
Joined
Sep 9, 2009
Messages
380
Welcome.

First press Alt F11 to give you access to the Macro VBA window. You should see (if not press F4) the project explorer with VBA Project (Book1) (but with the name of your spreadsheet instead of book1).

You need to hit Insert\Module. This will bring up the Module 1 window for your code. In there, paste the following:

Code:
Sub try()
x = 15
Do Until x = 198
If Cells(x, 6) = 0 And Cells(x, 7) = 0 Then
Rows(x).Hidden = True
End If
x = x + 1
Loop
End Sub

Sub undotry()
Rows("15:197").Hidden = False
End Sub

This will give you the macro for hiding the empty rows. The undotry bit unhides all the rows for you to start again the next day. All you have to do then is put two buttons into your spreadsheet and assign these macros to them

Good luck!
 

Steeviee

Active Member
Joined
Sep 9, 2009
Messages
380
how do i add the buttons, i tried run macro and nothing happened?

Right click on the toolbar and check Forms. This will give you a toolbar that includes a grey button. Click this and draw a button on your spreadsheet. It will automatically come up with the Assign Macro menu. Click on try and OK. Repeat the procedure for undotry. You can then highlight the text (Button1) and change it to a label of your choice. When you click this button, the macro should run.

Make sure that you have set your security to allow macros: Tools/Options. Choose the security tab and click on Macro security button. Set this to Medium. each time you open the spreadsheet, it will ask you if you want to allow macros - click yes to make the buttons work.
 

mauricelopes

New Member
Joined
Nov 30, 2009
Messages
9
thanks it worked, is there a way i can have another button to clear all contents in those cells?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,190
Messages
5,594,761
Members
413,931
Latest member
acrato

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