默认情况下,我在 Excel 中隐藏了第 9 行。但是,如果用户从下拉列表 (A3) 中选择 Tom 或 Patrick,我想取消隐藏此行。否则,我希望始终隐藏此行。我该如何编写代码?
先感谢您!
答案1
根据下拉列表隐藏行
- 将其复制到
Sheet1
您想要发生这种情况的工作表的工作表模块(例如)中。
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Const CellAddress As String = "A3"
Const StringsList As String = "Patrick,Tom"
Const HideRow As Long = 9
If Not Intersect(Target, Range(CellAddress)) Is Nothing Then
Dim Strings() As String: Strings = Split(StringsList, ",")
Dim rrg As Range: Set rrg = Rows(HideRow)
Dim cIndex As Variant
cIndex = Application.Match(Target.Value, Strings, 0)
If IsNumeric(cIndex) Then
If rrg.Hidden Then
rrg.Hidden = False
End If
Else
If Not rrg.Hidden Then
rrg.Hidden = True
End If
End If
End If
End Sub