Try to Increment "To" Variable in "For To" but it's static.

[D_Marcel]

Dears,

Greetings!
I'll not present a problem that I need to solve, but something I realized after using the "For To" loop today.
This is the code originally designed:

[VBA]Sub Break_List()

Dim Index As Long
Dim Rows_Counter As Long


Rows_Counter = ActiveSheet.UsedRange.Rows.Count
For Index = 70274 To Rows_Counter
Cells(Index, 2).Select
If Cells(Index, 2).Value <> Cells(Index - 1, 2).Value Then
Cells(Index, 2).EntireRow.Insert
Rows_Counter = ActiveSheet.UsedRange.Rows.Count
Index = Index + 1
End If
Next Index
End Sub[/VBA]

Before enter in the loop (For Index...), the value determined to the variable "Rows_Counter" is 70274. However, as new lines is being inserted inside the loop, its value is incremented as long as the code is running. I was expecting that the code will run until the last row, 70585 after all the new lines, but it stops at 70274.

Do someone knows why this happen?

Thanks a lot!

Kind regards,

Douglas Marcel de Moraes

[Kenneth Hobs]

If it is not a problem to solve then there should be no question.

As you found, the counter's end value is static. Use a Do loop or other method. When I add or delete rows, I like to use a negative step: e.g. For i = 3 to 1 Step -1.

[SamT]

1. Rows_Counter = ActiveSheet.UsedRange.Rows.Count
2. For Index = 70274 To Rows_Counter
3. Cells(Index, 2).Select
4. If Cells(Index, 2).Value <> Cells(Index - 1, 2).Value Then
5. Cells(Index, 2).EntireRow.Insert
6. Rows_Counter = ActiveSheet.UsedRange.Rows.Count
7. Index = Index + 1
8. End If
9. Next Index

Line 2: "Index" is a Reserved Word in Excel or VBA; Do not use Reserved Words as Variables. At the very least give it a prefix, such as rIndex or RowIndex. I just use "r" as a Row indexer, following the Programming Standard that "i," "j," and "k" are loop indexers.

Line 4: You are starting at the bottom of the Range. I promise that with that algorithm, if the Structure actually worked, Rows would be inserted all the way to the bottom of the sheet

Line 6 You can prevent this by adding "Option Explicit" to the top of your Code Pages. VBA Tools>Options>Editor can be set to do this automatically

Lines 7 & 9: hmm, that can work, but there are usually better code structures.

Try this algorithm, which runs from bottom to top. The Structure is a Do While Loop

r = UsedRange.Rows.Count
Do While r > 2 'Assumes Header in Row 1
If Cells(r, 2).Value <> Cells(r - 1, 2).Value Then 
Rows(r).Insert
r = r - 1 'Skip the newly inserted Row
End If
r = r - 1
Loop

[D_Marcel]

I expressed myself poorly, Kenneth, sorry for disapoint you.

SamT, actually I posted the wrong code, sorry. Knowing that using "For Index = 2 to Rows_Counter" wasn't working as I expected, I set the code to run again from the last row processed.
Your code is much more simple and much more faster than mine.

Thank you very much for sharing it and also for the tips!

Guess that you already has the city that I live in your "wall". =P
I'll try to use more "Do Loop" or "Do While" instead of always use "For To".

Kind regards,