Cannot get my excel loop to move to the next cycle

Question

I want my Macro to loop every 3rd row.&nbsp; Here is the code I am using. It will run through the code find perform the functions great on the first pass, but on the second pass, the loop moves back to ("A1:B1")&nbsp;&nbsp;&nbsp;&nbsp;Sub Module2()
Dim i As Integer
NumRows = Range("A1", Range("A1").End(xlDown)).Rows.Count
Range("A1").Select

For i =1 To IsEmpty(ActiveCell)

Range("A1:B1").Select
    Selection.Delete Shift:=xlToLeft
    Range("A2:D2").Select
    Selection.Cut
    Range("B1").Select
    ActiveSheet.paste
    Range("A3").Select
    Selection.Cut
    Range("F1").Select
    ActiveSheet.paste
    ActiveCell.Offset(3, -5).Select
  Loop
    End Sub

Public Sub blanks()
 Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.Delete Shift:=xlUp
End Sub&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

hansvogelaar · Answer

webbk22&nbsp;
I don't see how this code could run at all - it is both syntactically and logically incorrect.
What is it supposed to do?

jmb17 · Answer

webbk22&nbsp; It appears that you want to loop through the cells, in blocks of 3, delete the first cell (column A &amp; B, shifting to the left), move the second cell over one column (to B), and move the third cell over six columns (to F)?&nbsp;If so, you could try this (be sure to back up your data first as you can't undo a vba action):Sub Module2()
     Dim i As Long
     Dim rng As Range
     
     Set rng = Range("A1", Range("A1").End(xlDown))
     
     With rng
          If .Rows.Count Mod 3 &gt; 0 Then Exit Sub
     
          For i = rng.Rows.Count To 1 Step -3
               .Cells(i, 1).Cut .Cells(i, 1)(1, 6)
               .Cells(i - 1, 1).Cut .Cells(i - 1, 1)(1, 2)
               .Cells(i - 2, 1).Resize(1, 2).Delete shift:=xlToLeft
          Next i
     End With

End Sub

Forum Discussion

Cannot get my excel loop to move to the next cycle

2 Replies

Resources