Excel VBA 代码（分配给按钮）根据跨多个工作表的单元格值隐藏/取消隐藏行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20630999/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA code (assigned to a button) to hide/unhide rows based on cell values across multiple sheets
提问by user3110580
this is my first post here and additionally I also have completely no knowledge on VBA whatsoever... so please excuse my ignorance ;-)
这是我在这里的第一篇文章，另外我对 VBA 一无所知......所以请原谅我的无知;-)
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.
如果有人可以提出一个适当的脚本来在多张纸上分别执行此操作，我将不胜感激。同样为了避免在打印预览后脚本运行速度慢 100 倍时出现的问题。
Thanks in advance.
提前致谢。
采纳答案by Michael
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.
Range("BOQ")指的是第一张纸上的范围。因此，无论选择哪个工作表，宏都会影响工作表 1 上的该范围。
To make the code flexible to the sheet you're on, consider changing it to something like:
为了使代码对您所在的工作表灵活，请考虑将其更改为以下内容：
Activesheet.Range("A2:A10")
Activesheet.Range("A2:A10")
