For Loop steps issue

[Brock_Hardchest]

Hello,

I am using a for loop to move some rows around. The loop skips rows as if it is stepping by 2. I have "Step 1" added and yet it still skips rows. Any ideas?

j = 2
For i = 2 To row1 Step 1
    If Range("R" & i) < 0 And Range("W" & i) < 0 Then
        Sheets("Credit & Rebills Data").Range("A" & j).EntireRow.Value = Sheets("Current Invoice Query").Range("A" & i).EntireRow.Value
        Sheets("Current Invoice Query").Range("A" & i).EntireRow.Delete
        j = j + 1
    End If
    
Next i

 

[joeu2004]

Try:

For i = row1 To 2 Step -1

I don't know if that will work with the rest of your code. I am not taking the time to try to understand it.

(I am concerned about what should happen with "j".)

But the point is: you need to delete rows from highest-number to lowest-number.

When you delete row 2, for example, row 3 becomes row 2, row 4 becomes row 3, etc.

Consequently, when "i" become 3, you might effectively delete row 4 because it is now row 3.

 

[Michael M]

Change this line to a negative

For i = row1 to 2 step -1

By going down tha page instead of up, the deleted row will cause your code to skip a row

 

[Brock_Hardchest]

Yes, that is my problem! Thanks y'all

 

[joeu2004]

I don't know if that will work with the rest of your code. I am not taking the time to try to understand it.
(I am concerned about what should happen with "j".)

Looking now at what you are trying to do, try the following code (untested), which allows you to advance "i" forward, as you require for copying to successive rows "j".

j = 2
i = 2
n = row1
Do
    If Range("R" & i) < 0 And Range("W" & i) < 0 Then
        Sheets("Credit & Rebills Data").Range("A" & j).EntireRow.Value = Sheets("Current Invoice Query").Range("A" & i).EntireRow.Value
        Sheets("Current Invoice Query").Range("A" & i).EntireRow.Delete
        j = j + 1
        n = n - 1
    Else
        i = i + 1
    End If
Loop Until i > n

ERRATA.... Changed the loop limit to i > n instead of i = n.

Thus, "i" is advanced only when we do not delete a row.

When we do delete a row, we do not want to advance "i" because the current row "i" is now the previous next row. (Clear?)

But note that we decrement the limit for "i" ("n" instead of row1). So eventually "i" and "n" will coincide.