6
Dim i As Long
Dim rows As Long
Dim rng3 As Range
rows = rng3.rows.Count
For i = rows To 1 Step (-1)

Does anyone know how this loop works? I'm confused on the meaning of rows To 1 Step (-1).

brettdj
  • 54,857
  • 16
  • 114
  • 177
usernolongerregistered
  • 390
  • 1
  • 4
  • 19
  • 1
    If you can, turn it around to -> for i = 1 to rows There usually isn't a good reason to count down and it makes the code needlessly complex. Some people use it when combined with offset, but I'm quite against using offset as well for code reviewing reasons. – html_programmer Oct 30 '13 at 15:36
  • 1
    I will mention that Sam's answer is obviously correct, hence I upvote his answer. Personally I find it more readable to increment a loop counter from a code review perspective and manage the decrement in the body, but that is just my point of view. – html_programmer Oct 30 '13 at 15:52
  • 2
    @KimGysen - If you're deleting rows then it's best to start from the bottom and work up... – Tim Williams Oct 30 '13 at 16:00
  • 1
    @TimWilliams Personally I would go with rng3.rows(row_count - i) in the body with incremental counter in the loop, because I think it is more intuitive for most programmers to read. But I don't have anything against it necessarily. I purely mentioned this because the OP says to be a beginning VBA-er; in the majority of the cases you would use the incremental counter, not turn it around unless it is unnecessary. – html_programmer Oct 30 '13 at 16:08
  • 1
    @KimGysen - as with many things it's a question of preference :-) – Tim Williams Oct 30 '13 at 16:14

3 Answers3

27

from high number To 1 adding (-1) each iteration

Note: It's adding because + AND - in mathematical logic evaluate to a -

If rows = 10 then

for i = 10 to 1 step -2 would mean loop back from 10 to 1 subtracting 2 from the i in each loop cycle.

adding a Debug.Print i inside the loop may give you a better clue.

Note: turn ON the Immediate Window hitting CTRL+G or View => Immediate Window from the VBE menu bar

enter image description here

An example loop increasing by 3 on each cycle.

for i = 1 to 10 step 3
    debug.print i 
next i

enter image description here

Usage

The step-back technique is mostly used when deleting rows from a spreadsheet.

To see the logic in practice see the following

Community
  • 1
  • 1
  • 1
    Thank you very much! This explanation was awesome :) Definitely broke it down for me in a very simple way. – usernolongerregistered Oct 30 '13 at 16:13
  • @jacouh that would be a great new question but you can go into Tools > Options in the menu bar and the second tab is where you can edit the colors –  Oct 30 '13 at 19:24
5

When deleting rows, it is often common practise to start at the end and step backwards, this is so no rows are skipped.

Dim i As Long
Dim rows As Long
Dim rng3 As Range

rows = rng3.rows.Count


For i = rows To 1 Step (-1)

    'delete row if "delete" is in column 1
    If rng3.cells(i,1).Value = "delete" Then

    rng3.Rows(i).EntireRow.Delete

    End If

next i
Sam
  • 7,245
  • 3
  • 25
  • 37
0
Dim i as Integer

For i = 1 To 14 Step 3
Debug.Print i
Next i

In above code loop will iterate from 1 to 14 increment with 3 so output will be like

1 4 7 10 13

It means it can not cross 14 that is limit.

So whatever value is provided in step it will add into the variable use for looping purpose. Here 

i = i +3

But in For loop in VBA, Step value can not be changed dynamically. For example:

Dim i As Integer

For i = 1 To 10 Step i
    Debug.Print i
Next i

Here, before starting iteration Step is equal to the value of i that is the default value i.e. 0. So i will increment like below:

i = i+ i => i = i+0

So i will not increment here and loop will iterate for ever.

Now for below code:

Dim i as Integer

For i = 1 To 14 Step i+1
Debug.Print i
Next i

i will increment like : 

i=i+(i+1) => i= i+(0+1) =>i = i+1

so it will increment by 1 and output will be 1 2 3 .... 14

Now for below code :

Dim i As Integer
i = 3
For i = 1 To 10 Step i
    Debug.Print i
Next i

here, i is equal to 3 before loop execution, so Step value will be 3, but loop will start with i = 1 and will increment with 3 through out the loop. here, 

i = i+3

so output will be 1 4 7 10.

Now for some other variable:

Dim i As Integer
Dim j As Integer
j = 2

For i = 1 To 10 Step j
    Debug.Print i
    j = i
Next i

in above code Step value will be 2, so i will increment by 2 for every iteration whether j is modifying inside loop or not, it will not impact Step value, so output will be

1 3 5 7 9

Please correct me if I miss anything or something is wrong in this. Also suggest if there is any way for dynamic looping using For loop in VBA.

DHARMRAJSINGH
  • 11
  • 2