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
Loop forward:
For i = 1 To 10
'Do something
Next i
Loop backwards (use Step -1
at the end of the for loop):
For i = 10 To 1 Step -1
'Do something
Next i
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With