title: VBA error: Why does my code keep jumping back lines?
body: <p>I apologise for the code dump, but after two days of debugging, I think I'm starting to lose it, and I'm getting desperate. I have developed the code shown below</p>
  
  <pre><code>intColumnCount = wsStaff.Cells(1, Columns.Count).End(xlToLeft).Column  
  intColumnLoop = 2  
  intStaffCount = 0  
  wsDisplay.Range("A2").EntireRow.UnMerge  
  wsDisplay.Range("A2").EntireRow.Value = ""  
  Do  
    intRowCount = wsStaff.Cells(Rows.Count, intColumnLoop).End(xlUp).Row  
    intRowLoop = 2  
    Do  
      If IsEmpty(wsStaff.Cells(intRowLoop, intColumnLoop)) And intRowLoop &lt;&gt; 2 Then  
        wsStaff.Range(wsStaff.Cells(intRowLoop, intColumnLoop).Address).Delete Shift:=xlUp  
        intRowCount = wsStaff.Cells(Rows.Count, intColumnLoop).End(xlUp).Row  
      Else  
        intStaffCount = intStaffCount + 1  
        If wsDisplay.Cells(1, intStaffCount + 2).Value &lt;&gt; wsStaff.Cells(intRowLoop, intColumnLoop).Value And wsDisplay.Cells(1, intStaffCount + 2).Value &lt;&gt; wsStaff.Cells(intRowLoop + 1, intColumnLoop).Value And wsDisplay.Cells(1, intStaffCount + 2).Value &lt;&gt; wsStaff.Cells(2, intColumnLoop + 1).Value And wsDisplay.Cells(1, intStaffCount + 2).Value &lt;&gt; "" Then  
          wsDisplay.Range(wsDisplay.Cells(1, intStaffCount + 2).Address).EntireColumn.Delete Shift:=xlToLeft  
          intRowCount = wsStaff.Cells(Rows.Count, intColumnLoop).End(xlUp).Row  
          intStaffCount = intStaffCount - 1  
        ElseIf wsDisplay.Cells(1, intStaffCount + 2).Value = wsStaff.Cells(intRowLoop + 1, intColumnLoop).Value Or wsDisplay.Cells(1, intStaffCount + 2).Value = wsStaff.Cells(2, intColumnLoop + 1).Value Or wsDisplay.Cells(1, intStaffCount + 2).Value = "" Then  
          wsDisplay.Range(wsDisplay.Cells(1, intStaffCount + 2).Address).EntireColumn.Insert Shift:=xlToRight  
          wsDisplay.Cells(1, intStaffCount + 2).Value = wsStaff.Cells(intRowLoop, intColumnLoop).Value  
          intRowLoop = intRowLoop + 1  
        Else  
          intRowLoop = intRowLoop + 1  
        End If  
        If wsDisplay.Cells(1, intStaffCount + 2).Value = wsDisplay.Cells(1, intStaffCount + 1).Value Then  
          wsDisplay.Range(wsDisplay.Cells(1, intStaffCount + 2).Address).EntireColumn.Delete Shift:=xlToLeft  
          intRowCount = wsStaff.Cells(Rows.Count, intColumnLoop).End(xlUp).Row  
          intStaffCount = intStaffCount - 1  
        End If  
        wsDisplay.Cells(1, intStaffCount + 2).Interior.Color = RGB(255 - (0.1 * (255 - intColourPalette(intColumnLoop Mod 6 + 1, 1))), 255 - (0.1 * (255 - intColourPalette(intColumnLoop Mod 6 + 1, 2))), 255 - (0.1 * (255 - intColourPalette(intColumnLoop Mod 6 + 1, 3))))  
      End If  
    Loop While Not intRowLoop &gt; intRowCount  
    wsDisplay.Range(wsDisplay.Cells(2, 4 + intStaffCount - intRowCount).Address, wsDisplay.Cells(2, 2 + intStaffCount).Address).Merge  
    wsDisplay.Range(wsDisplay.Cells(2, 4 + intStaffCount - intRowCount).Address).Interior.Color = RGB(intColourPalette(intColumnLoop Mod 6 + 1, 1), intColourPalette(intColumnLoop Mod 6 + 1, 2), intColourPalette(intColumnLoop Mod 6 + 1, 3))  
    wsDisplay.Cells(2, 4 + intStaffCount - intRowCount).Value = wsStaff.Cells(1, intColumnLoop).Value  
    wsDisplay.Cells(2, 4 + intStaffCount - intRowCount).Font.Bold = True  
    wsDisplay.Cells(2, 4 + intStaffCount - intRowCount).Font.Color = RGB(255, 255, 255)  
    intColumnLoop = intColumnLoop + 1  
  Loop While Not intColumnLoop &gt; intColumnCount  
  wsDisplay.Cells(1, 1).EntireRow.Orientation = -45  
  wsDisplay.Cells(1, 1).EntireRow.HorizontalAlignment = xlRight  
  wsDisplay.Range(wsDisplay.Cells(1, 3), wsDisplay.Cells(2, intStaffCount + 2)).Borders.LineStyle = xlContinuous  
  intDisplayRowLength = wsDisplay.Cells(1, Columns.Count).End(xlToLeft).Column  
  intEraser = intStaffCount + 3  
  wsDisplay.Range(wsDisplay.Cells(1, intEraser), wsDisplay.Cells(1, intDisplayRowLength)).EntireColumn.Delete Shift:=xlToLeft  
  End Sub
  </code></pre>
  
  <p>So I have two issues, both involve the code jumping back lines and reperforming code. I figured this out by stepping through the code hundreds of times with almost all the code on breakpoints under different variable conditions.</p>
  
  <p>The code frequently would jump back to the start whenever intRowCount was redefined, this error is not harmful to the output but it does dramatically increase computational time. I understand this may be built in to the Do loops and so is inconvenient but not the biggest issue.</p>
  
  <p>The biggest issue is the second last line, after deleting all the unnecessary columns the code will jump back to the last end if statement, which is inside the nested do Loop. I have no idea why it is jumping back, on top of that it seems that the intRowCount, intRowLoop, intColumnCount and intColumnLoop variables change to allow repeat loops of the code.</p>
  
  <p>The reason this is so catastrophic is because the intStaffCount doesn't change, meaning data is being added twice.</p>
  
  <p>If anyone can provide any insight It would be greatly appreciated.</p>
  
  <p>Edit: It is jumping to the position in the code that a new item was added and chooses to infinitely do so whenever it reaches the second last line.</p>
  
  <p>Edit 2: It actually is reaching the End Sub line, it's just not ending</p>
  
  <p>Edit 3: Solution found external to the code shown, events had to be disabled before the sub was called and re-enabled after</p>