How to reverse a For loop

excel, vba

Solution

If you're looping and deleting rows, you need to start at the bottom and work up:

For x = endrow To startrow step -1
   'Execute code
Next x

Then deleting rows will not disrupt your loop.

Problem

I have a problem with my macro. It deletes row that fullfil certain criteria. But when few consecutive rows fullfil those criteria an error occurs. When the row is deleted the other rows are shifted up so if i.e. row(2:2) and row(3:3) fullfil the criteria then the row(2:2) is deleted and row(3:3) is shifted up, so it becomes row(2:2), and For loop goes to another row (third one). As a result row that used to be row(3:3) and now is row(2:2) is omitted and not deleted. In order to deal with this topic I think that it is enough to reverse to For loop, so it wouldn't go from up to bottom but from bottom to top. Te resulat would be double checking of some rows, but no rows would be omitted. The proble is that I don't know how to revese the For loop. I have tried to change 'For x = startrow To endrow' to 'For x = endrow To startrow', but it didn't help. Here is the code: ``` Sub Repurchase_upload() Dim Worksheet As Worksheets startrow = Worksheets("GUTS").Cells(10, 1) endrow = Worksheets("GUTS").Cells(11, 1) For x = startrow To endrow 'I have tried to change this line into: 'For x = endrow To startrow', but it didn' help If Cells(x, "A").Value <> "AA" And Cells(x, "A").Value <> "AB" And Cells(x, "A").Value <> "AC" And Cells(x, "A").Value <> "AD" And Cells(x, "A").Value <> "AE" And Cells(x, "A").Value <> "AH" And Cells(x, "A").Value <> "AI" And Cells(x, "A").Value <> "AF" And Cells(x, "A").Value <> "AG" Then Cells(x, "A").EntireRow.Delete End If Next End Sub ``` Thank you all a lot in advance, with best regards, Artur Rutkowski

Original source