move onto next iteration of loop in excel vba

Question

I have a loop with several If statements in Excel VBA. This goes through and hides certain rows based on certain criteria. Basicially, if one of the statements is true then the row is hidden. Since only one of the statements has to be true for the row to be hidden it would be pointless for the rest of the statements to be tested once one of the statements is found to be true. How would I put in a line of code that would say to move onto the next iteration of the loop once the if statement is found to be true? Any help would be greatly appreciated!

For i = 1 To rng2.Rows.Count

    If Left(rng3.Cells(i, 1).Value, 8) = "CMS Part" Then
        If rng3.Cells(i, 1).Value <> "CMS Part D (CY " & Year(Date) & ")" Then
            rng3.Cells(i, 1).EntireRow.Hidden = True

        End If
    End If

    If rng4.Cells(i, 1).Value = "Yes" Then
        rng4.Cells(i, 1).EntireRow.Hidden = True

    End If

    If InStr(1, CStr(rng5.Cells(i, 1).Value), "test") > 0 Or InStr(1, CStr(rng5.Cells(i, 1).Value), "Test") > 0 Or InStr(1, CStr(rng5.Cells(i, 1).Value), "demo") > 0 Or InStr(1, CStr(rng5.Cells(i, 1).Value), "Demo") > 0 Or InStr(1, CStr(rng5.Cells(i, 1).Value), "TEST") > 0 Or InStr(1, CStr(rng5.Cells(i, 1).Value), "DO NOT USE") > 0 Then
        rng5.Cells(i, 1).EntireRow.Hidden = True

    End If

Next i

Use either a Select Case construct, or use an IF .... ElseIf .... ElseIf... End If construct — tigeravatar
– tigeravatar, Commented Mar 2, 2015 at 18:43
This is clearly a case for If...ElseIf; Select Case would be a bad choice here. — Mathieu Guindon
– Mathieu Guindon, Commented Mar 2, 2015 at 19:08

Kyle · Accepted Answer · 2015-03-02 19:10:39Z

1

I know using goto statements is generally bad programming, but an option would be:

For i = 1 To rng2.Rows.Count

If Left(rng3.Cells(i, 1).Value, 8) = "CMS Part" Then
    If rng3.Cells(i, 1).Value <> "CMS Part D (CY " & Year(Date) & ")" Then
        rng3.Cells(i, 1).EntireRow.Hidden = True
        Goto Skip
    End If
End If

If rng4.Cells(i, 1).Value = "Yes" Then
    rng4.Cells(i, 1).EntireRow.Hidden = True
    Goto Skip
End If

If InStr(1, CStr(rng5.Cells(i, 1).Value), "test") > 0 Or InStr(1, CStr(rng5.Cells(i, 1).Value), "Test") > 0 Or InStr(1, CStr(rng5.Cells(i, 1).Value), "demo") > 0 Or InStr(1, CStr(rng5.Cells(i, 1).Value), "Demo") > 0 Or InStr(1, CStr(rng5.Cells(i, 1).Value), "TEST") > 0 Or InStr(1, CStr(rng5.Cells(i, 1).Value), "DO NOT USE") > 0 Then
    rng5.Cells(i, 1).EntireRow.Hidden = True
    Goto Skip
End If

Skip: Next i

edited Mar 2, 2015 at 19:10

answered Mar 2, 2015 at 18:57

Kyle

2,5452 gold badges19 silver badges33 bronze badges

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

1 Comment

Seb Over a year ago

You can improve readability of this by using something like "continue_0" as label

Steven Martin · Accepted Answer · 2015-03-03 08:40:42Z

I know others said it, but here is the code using elseif

For i = 1 To rng2.Rows.Count

    If Left(rng3.Cells(i, 1).Value, 8) = "CMS Part" Then
        If rng3.Cells(i, 1).Value <> "CMS Part D (CY " & Year(Date) & ")" Then
            rng3.Cells(i, 1).EntireRow.Hidden = True

        End If

    ElseIf rng4.Cells(i, 1).Value = "Yes" Then
        rng4.Cells(i, 1).EntireRow.Hidden = True

    ElseIf InStr(1, CStr(rng5.Cells(i, 1).Value), "test") > 0 Or InStr(1, CStr(rng5.Cells(i, 1).Value), "Test") > 0 Or InStr(1, CStr(rng5.Cells(i, 1).Value), "demo") > 0 Or InStr(1, CStr(rng5.Cells(i, 1).Value), "Demo") > 0 Or InStr(1, CStr(rng5.Cells(i, 1).Value), "TEST") > 0 Or InStr(1, CStr(rng5.Cells(i, 1).Value), "DO NOT USE") > 0 Then
        rng5.Cells(i, 1).EntireRow.Hidden = True

    End If

Next i

Collectives™ on Stack Overflow

move onto next iteration of loop in excel vba

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related