Excel VBA循环用于行

Question

我想问一下如何在下面简化我的 VBA 代码。
是否可以通过使用“循环”来实现？谢谢 :)

例如(但不起作用):

Dim i As Integer

For i = 0 To 6

If Range("A126+i").Value = "" Then
Rows("127+i:129").EntireRow.Hidden = True

Else

Rows("127:128+i").EntireRow.Hidden = False

End If

我的代码:

If Range("A120").Value = "" Then                       
    Rows("121:126").EntireRow.Hidden = True
    Else
    Rows("121:122").EntireRow.Hidden = False
End If

If Range("A121").Value = "" Then
   Rows("122:126").EntireRow.Hidden = True
    Else
    Rows("122:122").EntireRow.Hidden = False
End If

 If Range("A122").Value = "" Then
    Rows("123:126").EntireRow.Hidden = True
    Else
    Rows("122:123").EntireRow.Hidden = False
End If

  If Range("A123").Value = "" Then
    Rows("124:126").EntireRow.Hidden = True
    Else
    Rows("122:124").EntireRow.Hidden = False
End If

If Range("A124").Value = "" Then
    Rows("125:126").EntireRow.Hidden = True
    Else
    Rows("122:125").EntireRow.Hidden = False
End If

If Range("A125").Value = "" Then
    Rows("126:126").EntireRow.Hidden = True
    Else
    Rows("122:126").EntireRow.Hidden = False
End If

Answer 1

这应该使您的初始循环正常工作:

Public Sub TestMe()

    Dim i As Long

    For i = 0 To 6
        If Range("A" & 126 + i).Value = "" Then
            Rows(127 + i & ":129").EntireRow.Hidden = True
        Else
            Rows("127:" & 128 + i).EntireRow.Hidden = False
        End If
    Next i

End Sub

这个想法是正确连接范围。它们包含字符串和整数值。因此 A & 126+i应该是 "A" & 126 + i .或者你甚至可以省略条件并用更少的行来做:

Public Sub TestMe()
    Dim i As Long
    For i = 0 To 6
        Rows(127 + i & ":129").EntireRow.Hidden = CBool(Range("A" & 126 + i) = "")
        Rows("127:" & 128 + i).EntireRow.Hidden = CBool(Range("A" & 126 + i) = "")
    Next i
End Sub