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

[mauricelopes]

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

 

[Domski]

You could maybe use Autofilter to achieve this without messing about with VBA.

Dom

 

[Steeviee]

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:

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!

 

[mauricelopes]

how do i add the buttons, i tried run macro and nothing happened?

 

[mauricelopes]

i got it to work thanks

 

[Steeviee]

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]

thanks it worked, is there a way i can have another button to clear all contents in those cells?