Looping Code/Skipping Rows

Question

I have the following in a piece of VBA code:

For i = 1 To 5
    myArray(i) = i + 0
Next i
For i = 6 To 8
    myArray(i) = i + 1
Next i
For i = 9 To 14
    myArray(i) = i + 2
Next i

...etc

There must be a better way to do this, but for the life of me I cannot think of one. Could anyone help? Perhaps something with the Select..Case syntax?

Edit: these are actually supposed to be various sub-divisions of areas in the company. the lines I am 'skipping' are automatically calculated totals of said areas.

Answer 1

Since the rows you're skipping aren't uniform and, from the sound of it, may change you probably don't want to hard code them in several rows of a Select statement. It might be easier to record them in an Array:

'Create an Array of Rows to Skip
rowsToSkip = Array(6, 9)

'Loop over all data
For i = 1 To 14
    skipcount = 0

    'For each skip row we have passed add one to the skipcount
    For Each rownum In rowsToSkip
        If i >= rownum Then
            skipcount = skipcount + 1
        End If
    Next rownum

    myArray(i) = i + skipcount
Next i

Answer 2

For i = 1 to 14
    select case i
        case 1 to 5
            myArray(i) = i + 0
        case 6 to 8
            myArray(i) = i + 1
        case 9 to 14
            myArray(i) = i + 2
    end select 
Next i