Search code examples
excelloopsis-emptyvba

How can I expand a Do Until IsEmpty Loop for two loops more?

In Excel I have a column of data. I want to use this column of data for a loop. The code below stops when it hits 1 empty row. However I need the loop to proceed for two steps more.

i = 1
Do Until IsEmpty(ActiveWorkbook.Worksheets("Data").Cells(i, 1).Value2)
    'do something...
i = i + 1
Loop

In other words, is there something like:

Do Until IsEmpty(ActiveWorkbook.Worksheets("Data").Cells(i, 1).Value2) + 2

I hope my question is clear!

Solution

  • You'll need a new variable to track this. You can do it in the existing DO UNTIL loop:

    i = 1
extraTwo = 1
Do Until IsEmpty(ActiveWorkbook.Worksheets("Data").Cells(i, 1).Value2) or extraTwo > 2
    'do something...
    i = i + 1
    if IsEmpty(ActiveWorkbook.Worksheets("Data").Cells(i, 1).Value2) then extraTwo = extraTwo + 1
Loop

    Or in an outside loop:

    i = 1
Do Until IsEmpty(ActiveWorkbook.Worksheets("Data").Cells(i, 1).Value2)
    'do something...
    i = i + 1
Loop

For extraTwo = 1 to 2
    'do something...
    i = i + 1
    extraTwo = extraTwo + 1
Next

    The first one means no code duplication (even if you move do something... to a function or subroutine you have to duplicate the call), but the second is slightly more obvious what is happening (although that's debatable).