vba Excel 根据单元格值自动隐藏/取消隐藏行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26801546/
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 Autohide/Unhide rows based on Cell Value
提问by Matt
I have an excel form that i wan to hide rows 43 to 49 if the value in cell B22 is equal to or greater than 25000.
我有一个 excel 表单，如果单元格 B22 中的值等于或大于 25000，我想隐藏第 43 到 49 行。
I have this code so far but it seems to just crash excel and i have to escape out of it.
到目前为止，我有这段代码，但它似乎只是让 excel 崩溃，我必须摆脱它。
Private Sub Worksheet_Calculate()
Rows("43:49").EntireRow.Hidden = False
Select Case Range("B22").Value
Case Is >= 25000
Rows("43:49").EntireRow.Hidden = True
Case Else
Rows("43:49").Select
Selection.EntireRow.Hidden = False
End Select
End Sub
采纳答案by Siddharth Rout
Don't use Worksheet_Calculate. Use Worksheet_Change
不要使用Worksheet_Calculate. 用Worksheet_Change
See this example. Move about Worksheet_Changehere
请参阅此示例。在Worksheet_Change这里走动
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Whoa
'~~> Change CountLarge to Count if using xl2003
If Target.Cells.CountLarge > 1 Then Exit Sub
Application.EnableEvents = False
If Not Intersect(Target, Range("B22")) Is Nothing Then
Select Case Target.Value
Case Is >= 25000: Rows("43:49").EntireRow.Hidden = True
Case Else: Rows("43:49").EntireRow.Hidden = False
End Select
End If
Letscontinue:
Application.EnableEvents = True
Exit Sub
Whoa:
MsgBox Err.Description
Resume Letscontinue
End Sub
回答by ZAT
You could try this as well:
你也可以试试这个：
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B" Then
If Target.Value >= 25000 Then Rows("43:49").EntireRow.Hidden = True
If Target.Value < 25000 Then Rows("43:49").EntireRow.Hidden = False
End If
End Sub
