vba understand step meaning

[Dancarro]

Hi,

I am trying to understand what does the vba do in the highlighted section:

For i = LastRow To 2 Step -1
If WorksheetFunction.CountIf(Range(Cells(2, "C"), Cells(i, "C")), Cells(i, "C")) > 1 Then
Rows(i).Delete
End If
Next I

Kind Regards,
Dan

 

[Joe4]

It is counting the number of times the values in column C of the current row appears in between row 2 and the current row in column C, to see if it is greater than 1 (if it is, it will delete that row).
So, it looks like this code is being used to delete duplicates (based on column C).

 

[Dancarro]

Hi Joe4,

What does 'To 2 Step -1' mean? Can you say 'For i = LastRow To 1'?

Kind Regards,
Dan

 

[shg]

What does 'To 2 Step -1' mean? Can you say 'For i = LastRow To 1'?

No; the default step for a loop variable is +1 unless otherwise specified. The run-time engine doesn't look to see what direction in needs to go.

It's behavior by design, and very appropriate.

 

[Joe4]

When deleting rows, it is always better to work backwards, from the bottom up. Otherwise, you may miss rows, as you delete and shift rows up into the range you have already checked.

 

[Dancarro]

Hi Joe4 and shg

So what it means it goes down to the last row and goes upto to the second row then one row down; delete row. Then again, second row then one row down. Is my understanding correct?

 

[Rick Rothstein]

Hi Joe4 and shg

So what it means it goes down to the last row and goes upto to the second row then one row down; delete row. Then again, second row then one row down. Is my understanding correct?

No... it starts at the last row then after checking it (and possibly deleting it), the loop then goes to the row above that and checks it (and possibly deletes it), then the loop goes to the row above that and checks it (and possibly deletes it), and so on until the loop arrives at Row 2 which, after checking it (and possibly deleting it), it stop executing. In other words, let's say the last row was 100... the code would start the loop there and then move to Row 99 and then to Row 98 and then to Row 97 on upward until it gets to Row 2 where it ends.

 

[Dancarro]

Hi Rick,

Thank you for the explanation