Excel VBA code (assigned to a button) to hide/unhide rows based on cell values across multiple sheets

Question

this is my first post here and additionally I also have completely no knowledge on VBA whatsoever... so please excuse my ignorance ;-)

I'm working on a price list which has a quantity column. The same files has multiple worksheets with multiple currencies. What I need to achieve is to create two buttons on each sheet to hide / unhide all rows where the quantity cell equals zero.

So for example you want to select certain items from the list, so you enter the quantity into appropriate cells (quantity column) and press the button to hide all other rows for which the quantity equals zero.

Now, I found the code for this somewhere already, but it only works on a first sheet and when I copy the sheet (to create another currency) with the buttons and press the button it will still apply the changes (hide / unhide rows) to the first sheet. This code is below:

Public Sub HideRows()
Dim cell As Range

For Each cell In Range("BOQ")

cell.EntireRow.Hidden = (cell.Value = 0 And cell.Value <> "")
Next cell
End Sub

and to unhide:

Public Sub UnhideRows()
Dim cell As Range

For Each cell In Range("BOQ")

If (cell.Value = 0 And cell.Value <> "") Then cell.EntireRow.Hidden = False
Next cell
End Sub

I would be extremely grateful if anyone could propose a proper script to do that separately on multiple sheets. Also to avoid the issue when after a print preview the script runs like a 100 times slower.

Thanks in advance.

Michael Michael · Accepted Answer · 2013-12-17T13:41:30

Range("BOQ") refers to a range on the first sheet. So no matter which sheet is selected, the macro will affect that range on sheet 1.

To make the code flexible to the sheet you're on, consider changing it to something like:

Activesheet.Range("A2:A10")

Excel VBA code (assigned to a button) to hide/unhide rows based on cell values across multiple sheets

1 Answers