CODEWITHSUNDEEP
vbaexcel
Artur Rutkowski
Artur Rutkowski

Reputation: 537

How to reverse a For loop

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

Upvotes: 22

Views: 68091

Answers (4)

Psychonaut3
Psychonaut3

Reputation: 26

This code has the syntax for deleting the entire row of your current selection.

It also starts at the bottom and steps the For loop in a descending manner.

Dim startingRow As Integer
Dim y As Integer
Dim checkGross As Double
    
    startingRow = Sheets("Stripe").Cells(Rows.Count, 13).End(xlUp).Row 'Gets the row number of the last record in column 13'
    Sheets("Stripe").Range("M" & startingRow).Select
    
    For y = startingRow To 3 Step -1 'Ending at 3 because Rows 2 and 1 are header rows-- Step -1 makes the y counter descend'
        checkGross = Sheets("Stripe").Range("M" & y).Value
        If checkGross < 0 Then
            Sheets("Stripe").Range("M" & y).EntireRow.Delete
        End If
    Next y

Upvotes: 0

dendarii
dendarii

Reputation: 3088

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.

Upvotes: 48

Cuinn Herrick
Cuinn Herrick

Reputation: 317

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

Upvotes: 11

ExcelExpert
ExcelExpert

Reputation: 362

Another alternative is to put x=x-1 after each delete. But working from the bottom up is better.

Upvotes: 2

Related Questions