Excel VBA - how to skip to next iteration in a Do Until...Loop

Question

I'm trying to skip to the next iteration in my vba code, with the 'continue' statement. Its not working..

  Do Until IsEmpty(xlSheet.Cells(row, 1))
       row = row + 1
       If xlSheet.Cells(row, 2) = "Epic" Then
          Continue
       End If
       xlSheet.Cells(row, 1).Value = 5
  Loop

Any idea?? Thanks..

YowE3K · Accepted Answer · 2016-10-05 07:52:02Z

8

VBA does not have a Continue statement. You can get around it by doing something like

Do Until IsEmpty(xlSheet.Cells(row + 1, 1))
   row = row + 1
   If xlSheet.Cells(row, 2) <> "Epic" Then
       xlSheet.Cells(row, 1).Value = 5
   End If
Loop

or

Do Until IsEmpty(xlSheet.Cells(row + 1, 1))
    row = row + 1
    If xlSheet.Cells(row, 2) = "Epic" Then
        GoTo ContinueDo
    End If
    xlSheet.Cells(row, 1).Value = 5
ContinueDo:
Loop

Note: In both versions I changed IsEmpty(xlSheet.Cells(row, 1)) to IsEmpty(xlSheet.Cells(row + 1, 1)) or else you will end up with an infinite loop.

answered Oct 5, 2016 at 7:52

YowE3K

24k7 gold badges28 silver badges45 bronze badges

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

2 Comments

Pav Over a year ago

Ah, my bad. I posted an answer based on VB keyword. Label is a nice alternative.

YowE3K Over a year ago

@pav - Yeah, I've done that on several occasions - googled something using "VBA" as part of the search terms, found a reference in MSDN, read it, posted an answer, and not realised that the MSDN page had nothing to do with VBA at all.

Rob Duikersloot · Accepted Answer · 2018-02-28 17:15:50Z

1

So this is another possibility that works for me. Just skip the unwanted lines...

Do Until IsEmpty(xlSheet.Cells(row, 1))
   row = row + 1
   Do while xlSheet.Cells(row, 2) = "Epic" 
      row = row + 1
   Loop
   xlSheet.Cells(row, 1).Value = 5
Loop

answered Feb 28, 2018 at 17:15

Rob Duikersloot

111 bronze badge

Comments

Caston · Accepted Answer · 2022-03-09 02:52:58Z

0

While Visual Basic has 3 continue statements for 3 different loops (Continue While, Continue Do, and Continue For), oddly Microsoft VBA does not use any of them. You are going to have to restructure your loops and conditionals to go without.

answered Mar 9, 2022 at 2:52

Caston

1779 bronze badges

Comments

Fernando Garcia · Accepted Answer · 2023-02-18 06:22:12Z

0

You can make something like this due to vba have not any continue statement using the Goto statement use for errors.

Do Until IsEmpty(xlSheet.Cells(row, 1))
       row = row + 1
       If xlSheet.Cells(row, 2) = "Epic" Then
          GoTo Exitloop
       End If
       xlSheet.Cells(row, 1).Value = 5
ExitLoop:
  Loop

answered Feb 18, 2023 at 6:22

Fernando Garcia

1

Collectives™ on Stack Overflow

Excel VBA - how to skip to next iteration in a Do Until...Loop

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related