根据多个条件隐藏行

Question

我正在创建一个文档，其中有一个项目列表，我想显示或隐藏，取决于某些下拉列表。
我遇到了一个问题，单元格E30中的一个下拉框是有条件的。

我需要:
如果E30 =' No' AND E6 =' VIC'
那么行1:45不隐藏行46:81是隐藏的
如果E30 ='Yes' AND E6 =' VIC'
那么行1:33不隐藏行34:81是隐藏的
如果E30 =' No' AND E6 ='OTHER'
那么行1:33和64:81不隐藏行34:63是隐藏的
如果E30 ='Yes' AND E6 ='OTHER'
那么行1:33不隐藏行34:81是隐藏的
如果E30 ='Yes' AND E6 ='OTHER'

下面是我到目前为止的代码。
第一组按设计工作。
第二个是上面的部分代码，我有一个问题。

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Activate
If Not Application.Intersect(Range("E19"), Range(Target.Address)) Is Nothing Then
Select Case Target.Value

Case Is = "NO":     Rows("34:81").EntireRow.Hidden = True
Rows("1:22").EntireRow.Hidden = False
Rows("23:33").EntireRow.Hidden = False
Case Is = "YES":    Rows("23:81").EntireRow.Hidden = True
Rows("1:22").EntireRow.Hidden = False

End Select
End If

ActiveSheet.Activate
If Not Application.Intersect(Range("E30"), Range(Target.Address)) Is Nothing Then
Select Case Target.Value

Case Is = "YES":    Rows("34:81").EntireRow.Hidden = True
Rows("1:33").EntireRow.Hidden = False

Case Is = "NO":     Rows("34:63").EntireRow.Hidden = True
Rows("1:33").EntireRow.Hidden = False
Rows("64:81").EntireRow.Hidden = False

End Select
End If
End Sub

Answer 1

工作表更改:基于多个条件隐藏行

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Range("E19"), Target) Is Nothing Then
Select Case UCase(CStr(Range("E19").Value))
Case "YES"
Rows("1:22").Hidden = False
Rows("23:81").Hidden = True
Case "NO"
Rows("1:22").Hidden = False
Rows("23:33").Hidden = False
Rows("34:81").EntireRow.Hidden = True
'Case Else
End Select
End If
'Yes
'IF E30 = 'Yes' AND E6 = 'VIC'
'Then Rows 1:33 are not hidden AND Rows 34:81 are hidden
'IF E30 ='Yes' AND E6 ='OTHER'
'Then Rows 1:33 are not hidden AND Rows 34:81 are hidden
'No
'IF E30 = 'No' AND E6 = 'VIC'
'Then Rows 1:45 are not hidden AND Rows 46:81 are hidden
'IF E30 = 'No' AND E6 = 'OTHER'
'Then Rows 1:33 and 64:81 are not hidden AND Rows 34:63 are hidden

If Not Intersect(Range("E6,E30"), Target) Is Nothing Then
Select Case UCase(CStr(Range("E30").Value))
Case "YES"
Select Case UCase(CStr(Range("E6").Value))
Case "VIC", "OTHER"
Rows("1:33").Hidden = False
Rows("34:81").Hidden = True
'Case Else
End Select
Case "NO"
Select Case UCase(CStr(Range("E6").Value))
Case "VIC"
Rows("1:45").Hidden = False
Rows("46:81").Hidden = True
Case "OTHER"
Rows("1:33").Hidden = False
Rows("34:63").Hidden = True
Rows("64:81").Hidden = False
'Case Else
End Select
'Case Else
End Select
End If
End Sub

Answer 2

下面是代码-请务必将Sheet1替换为您的工作表。

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$E$6" Or Target.Address = "$E$30" Then
If Sheet1.Range("E30").Value = "No" And Sheet1.Range("E6").Value = "VIC" Then

Sheet1.Range("A1:A45").Rows.EntireRow.Hidden = False
Sheet1.Range("A46:A81").Rows.EntireRow.Hidden = True

ElseIf Sheet1.Range("E30").Value = "Yes" And Sheet1.Range("E6").Value = "VIC" Then

Sheet1.Range("A1:A33").Rows.EntireRow.Hidden = False
Sheet1.Range("A34:A81").Rows.EntireRow.Hidden = True

ElseIf Sheet1.Range("E30").Value = "No" And Sheet1.Range("E6").Value = "OTHER" Then

Sheet1.Range("A1:A33").Rows.EntireRow.Hidden = False
Sheet1.Range("A64:A81").Rows.EntireRow.Hidden = False
Sheet1.Range("A34:A63").Rows.EntireRow.Hidden = True

ElseIf Sheet1.Range("E30").Value = "Yes" And Sheet1.Range("E6").Value = "OTHER" Then

Sheet1.Range("A1:A33").Rows.EntireRow.Hidden = False
Sheet1.Range("A34:A81").Rows.EntireRow.Hidden = True

End If