Pick out excel cells from a long horizontal list based on an adjacent numerical value
Using a Select will definitely shorten up your code. 

While i &lt;= 300
    Set Rng = Range("J" &amp; i)
    Select Case Rng.Value
        Case 0, 1, vbNullString
            i = i + 1
        Case 2
            Rng.Offset(, -9).Resize(, 125).Copy
            Rng.Offset(, -9).Insert Shift:=xlDown
            i = i + 2
        Case 3
            Rng.Offset(, -9).Resize(, 125).Copy
            Rng.Offset(, -9).Insert Shift:=xlDown
            Rng.Offset(, -9).Resize(, 125).Copy
            Rng.Offset(, -9).Insert Shift:=xlDown
            i = i + 3

        ' etc.

    End Select
Wend


The next step would be to extract a method to eliminate all of the duplication. It should take in a Range parameter and perform your copy/insert. Basically anytime you hit Ctrl + C you should stop and write a method instead.