Excel VBA loop used to rows

Question

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

Vityata · Accepted Answer · 2018-03-27 10:14:23Z

3

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

answered Mar 27, 2018 at 10:14

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

RomanM Over a year ago

Thank you! Now It works. Yes I was looking for this, but I didnt know the right formula.

Collectives™ on Stack Overflow

Excel VBA loop used to rows

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related