vba 用于在 excel 2010 中隐藏行的宏
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6576423/
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
macro for Hide rows in excel 2010
提问by Benno Houben
I'm kinda new to programming in VBA. I read some stuff on the internet but i couldnt find what i need or couldnt get it working. my problem:
我对 VBA 编程有点陌生。我在互联网上阅读了一些东西，但我找不到我需要的东西或无法让它工作。我的问题：
in worksheet 'sheet 1' in cell B6 a value is given for how many years a project will be exploited.
在单元格 B6 的工作表“工作表 1”中，给出了项目将被利用的年数的值。
in worksheets 'sheet 2' and 'sheet 3' i made a spreadsheet for 50 years ( year 1 to year 50; row 7 to row 56).
在工作表“工作表 2”和“工作表 3”中，我制作了 50 年的电子表格（第 1 年到第 50 年；第 7 行到第 56 行）。
in cell b6 in 'sheet 1' i want to enter a value between 1 and 50. when the value is 49 i want to hide row 56 in 'sheet2' and 'sheet 3'. when the value is 48 i want to hide rows 55:56 in 'sheet2' and 'sheet 3', and so on. this is what i got so far but i cant get it to work automaticly when i change the value in cell B6:
在“工作表 1”的单元格 b6 中，我想输入一个介于 1 和 50 之间的值。当值为 49 时，我想隐藏“工作表 2”和“工作表 3”中的第 56 行。当值为 48 时，我想隐藏“sheet2”和“sheet 3”中的 55:56 行，依此类推。这是我到目前为止所得到的，但是当我更改单元格 B6 中的值时，我无法让它自动工作：
Sub test1()
If Range("sheet1!B6") = 50 Then
Rows("52:55").EntireRow.Hidden = False
Else
If Range("sheet1!B6") = 49 Then
Rows("55").EntireRow.Hidden = True
Else
If Range("sheet1!B6") = 48 Then
Rows("54:55").EntireRow.Hidden = True
End If: End If: End If:
End Sub
i hope someone can help me with my problem.
我希望有人能帮助我解决我的问题。
Thank you
谢谢
回答by lionz
You almost got it. You are hiding the rows within the active sheet. which is okay. But a better way would be add where it is.
你几乎明白了。您正在隐藏活动工作表中的行。没关系。但更好的方法是添加它的位置。
Rows("52:55").EntireRow.Hidden = False
becomes
变成
activesheet.Rows("52:55").EntireRow.Hidden = False
i've had weird things happen without it. As for making it automatic. You need to use the worksheet_change event within the sheet's macro in the VBA editor (not modules, double click the sheet1 to the far left of the editor.) Within that sheet, use the drop down menu just above the editor itself (there should be 2 listboxes). The listbox to the left will have the events you are looking for. After that just throw in the macro. It should look like the below code,
没有它，我会发生奇怪的事情。至于让它自动。您需要在 VBA 编辑器中的工作表宏中使用 worksheet_change 事件（不是模块，双击编辑器最左侧的 sheet1。）在该工作表中，使用编辑器本身正上方的下拉菜单（应该有2 个列表框）。左侧的列表框将包含您要查找的事件。之后只需放入宏即可。它应该看起来像下面的代码，
Private Sub Worksheet_Change(ByVal Target As Range)
test1
end Sub
That's it. Anytime you change something, it will run the macro test1.
就是这样。每当您更改某些内容时，它都会运行宏 test1。
回答by Tiago Cardoso
Well, you're on the right path, Benno!
好吧，你走在正确的道路上，Benno！
There are some tips regarding VBA programming that might help you out.
有一些有关 VBA 编程的提示可能会对您有所帮助。
Use always explicit references to the sheet you want to interact with. Otherwise, Excel may 'assume' your code applies to the active sheet and eventually you'll see it screws your spreadsheet up.
As lionz mentioned, get in touch with the native methods Excel offers. You might use them on most of your tricks.
Explicitly declare your variables... they'll show the list of methods each object offers in VBA. It might save your time digging on the internet.
始终使用对要与之交互的工作表的显式引用。否则，Excel 可能会“假设”您的代码适用于活动工作表，最终您会看到它搞砸了您的电子表格。
正如 Lionz 所提到的，请联系 Excel 提供的本机方法。您可能会在大多数技巧中使用它们。
显式声明您的变量...它们将显示每个对象在 VBA 中提供的方法列表。它可能会节省您在互联网上挖掘的时间。
Now, let's have a draft code...
现在，让我们有一个代码草案......
Remember this code must be within the Excel Sheet object, as explained by lionz. It only applies to Sheet 2, is up to you to adapt it to both Sheet 2 and Sheet 3 in the way you prefer.
请记住，此代码必须位于 Excel Sheet 对象中，如 lionz 所述。它仅适用于工作表 2，由您决定以您喜欢的方式使其适应工作表 2 和工作表 3。
Hope it helps!
希望能帮助到你！
Private Sub Worksheet_Change(ByVal Target As Range)
Dim oSheet As Excel.Worksheet
'We only want to do something if the changed cell is B6, right?
If Target.Address = "$B" Then
'Checks if it's a number...
If IsNumeric(Target.Value) Then
'Let's avoid values out of your bonds, correct?
If Target.Value > 0 And Target.Value < 51 Then
'Let's assign the worksheet we'll show / hide rows to one variable and then
' use only the reference to the variable itself instead of the sheet name.
' It's safer.
'You can alternatively replace 'sheet 2' by 2 (without quotes) which will represent
' the sheet index within the workbook
Set oSheet = ActiveWorkbook.Sheets("Sheet 2")
'We'll unhide before hide, to ensure we hide the correct ones
oSheet.Range("A7:A56").EntireRow.Hidden = False
oSheet.Range("A" & Target.Value + 7 & ":A56").EntireRow.Hidden = True
End If
End If
End If
End Sub
