![VBA 根据单元格值隐藏某些行](https://linux22.com/image/1624997/VBA%20%E6%A0%B9%E6%8D%AE%E5%8D%95%E5%85%83%E6%A0%BC%E5%80%BC%E9%9A%90%E8%97%8F%E6%9F%90%E4%BA%9B%E8%A1%8C.png)
我在第 9 行到第 38 行有一些信息。我需要根据单元格值隐藏这些行,例如 A8:
如果
A8=3
是,则仅显示第 9 至 11 行(共 38 行)如果
A8=9
是,则仅显示第 9 至 18 行(共 38 行)如果
A8=0
是则隐藏从 9 到 38 的所有行。
... 以此类推,只针对第 9 行至第 38 行。
这是我目前的代码:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim var As Variant
'Determine if change was made to cell A8
If Not Intersect(Target, Range("A8")) Is Nothing Then
'Get value
var = Range("A8").Value
var = var + 9
Debug.Print var
'Hide all rows
Rows("9:38").EntireRow.Hidden = True
For i = 9 To var
'Show Rows
Rows("9:i").EntireRow.Hidden = False
Debug.Print i
Next i
End If
End Sub
答案1
不需要循环:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim var As Long
If Not Intersect(Target, Range("A8")) Is Nothing Then
var = Range("A8").Value + 8
Rows("9:38").EntireRow.Hidden = True
If var > 8 Then
Rows("9:" & var).EntireRow.Hidden = False
End If
End If
End Sub
编辑#1:
对于 2 个区块:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim var As Long
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
If Not Intersect(Target, Range("A8")) Is Nothing Then
var = Range("A8").Value + 8
Rows("9:38").EntireRow.Hidden = True
If var > 8 Then
Rows("9:" & var).EntireRow.Hidden = False
End If
End If
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
If Not Intersect(Target, Range("A39")) Is Nothing Then
var = Range("A39").Value + 39
Rows("40:69").EntireRow.Hidden = True
If var > 39 Then
Rows("40:" & var).EntireRow.Hidden = False
End If
End If
End Sub