Hi - Apologies up front, I have tried to construct a step-by-step answer but I'm aware that it might come across as more complicated than I intended. Plain text isn't the best medium and the ability to send you a proper Excel file would be easier. Anyway, if you follow what follows one step at a time, it should work (again apologies if it seems badly worded by me). Go into your Excel workbook -->
- Check the tabs at the top of Excel – do you have one called “Developer” alongside others such as Home, Insert, Review, View and Help?
If so, go to step 3; if not go to step 2
- Click on the File TAB at the top left and choose options Customise Ribbon and in the right column that appears, pop a tick in the Developer check box, and press OK. You should now have a Developer TAB at the top next to Review and Help
- Make sure that you have two sheets (at least) – one for the data you want to work on and one for a couple of buttons we’re going to add.
I’ve called my sheets “Control” (for the buttons) and “Data Sheet” – if you’re not sure how to rename sheets, you click on the sheet name tabs at the bottom of the screen, right click your mouse, select rename and then rename them.
If you don’t like the sheet names I’m using, then choose your own but, make sure that you amend the small bit of code that follows in step 5 wherever you see the following (i.e. change the names in quotes wherever they appear in the code to match your sheet names):
Worksheets("Control").Select
and
Worksheets("Data Sheet").Select
- Click on the Developer option at the top and then on the Visual Basic option that appears underneath on the left
- A set of small windows will appear. If you right click in the left window on the words “Sheet1(Data Sheet)”, and select View Code, you will now be able to type into a largish window on the right.
Copy and Paste in the following code that appears between (but not including) the s below (the bits that will appear in green are purely for comments to explain what each line does) – note it looks messy below but will look different when you paste it across:
Sub HideRowsWithZeroInColumnB()
Worksheets("Data Sheet").Select
'Moves control to the sheet with the data in
Dim LastRow, RowCount As Long
'Sets up a few variables to remember things as the program runs
LastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
'Gets the last row in the table
Call Un_HideRowsWithZeroInColumnB
'Calls the code in the block below to reset the data to restore any previously hidden rows
For RowCount = 1 To LastRow
'Starts at the first row of data
If Cells(RowCount, 2) = 0 Then
'Looks at each row and sees if there is a zero in column B
Rows(RowCount).Hidden = True
'Hides each row that has a zero in column B
End If
Next
'Moves onto the next row
Worksheets("Control").Select
'Moves control back to the sheet with the two buttons on it
End Sub
Sub Un_HideRowsWithZeroInColumnB()
Worksheets("Data Sheet").Select
'Moves control to the sheet with the data in
Rows.EntireRow.Hidden = False
''Resets the data to restore any previously hidden rows
Worksheets("Control").Select
'Moves control back to the sheet with the two buttons on it
End Sub
*
6. Then click the red cross in the top right of the code window and return to “normal” Excel
7. Click on the Developer option at the top again and then Insert (it’s a picture of briefcase) under Developer
8. Under Form Controls, choose the top left image of a button and using the mouse, draw a button on the screen.
Repeat for a second button next to the first.
9. Right click each button in turn and choose Edit Text. Make the first button read something like “Hide Rows with Zeros in Column B” and the second “Un-Hide all Rows” (a reset option).
10. And the final stages: right click the first button and select Assign Macro. In the choices box that appears, click on the name with the phrase “HideRowsWithZeroInColumnB()” in it (it might also have the name of your file in) but either way, choose the one with “HideRowsWithZeroInColumnB()” and press OK
11. Right click the second button and select Assign Macro. In the choices box that appears, click on the name with the phrase “UnHideRowsWithZeroInColumnB()” in it (it might also have the name of your file in) but either way, choose the one with “UnHideRowsWithZeroInColumnB()” and press OK.
Save your file BUT in the File Save As options, choose file type as “Excel Macro-enabled Workbook (.xlsm)” – if you don’t, your new buttons won’t work.
Now, if you put some data in the data sheet and then go to your control (buttons) sheet, it should hide and unhide as you want it to.
Apologies if it seems a lot to do and I’m hoping I’ve not missed much – not easy on a phone. PM me if you need any help tomorrow.