CODEWITHSUNDEEP
excelvba
RomanM
RomanM

Reputation: 29

Excel VBA loop used to rows

I would like to ask about How can I simplify my VBA code below. Is it possible to make it by using "Loop"? Thanks :)

For expample (but doesnt work): 

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

My code:

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

Upvotes: 1

Views: 49

Answers (1)

Vityata
Vityata

Reputation: 43575

This should make your initial loop working:

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

The idea is to concatenate correctly the ranges. They contain of String and integer value. Thus A & 126+i should be "A" & 126 + i. Or you may even omit the condition and do it with less lines:

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

Upvotes: 3

Related Questions