CODEWITHSUNDEEP
vbaexcelfor-loopdelete-row
lisarko8077
lisarko8077

Reputation: 307

Vba my for loop deletes only half of rows

i am doing a macro for a betting calculation. I have the following double for loop for deleting rows that match the if but it deletes only half of the rows that really matches

Sub calc()

    Dim db As String
    db = Worksheets("PRONO").Range("A1").Value

    Dim alldata As Workbook
    Dim i1 As Worksheet

    Set alldata = Workbooks(db)
    Set i1 = alldata.Worksheets("I1")



    For Each Match In Workbooks("BET EXCEL.xlsm").Worksheets("TEST").Range("A14:" & Range("A14").End(xlDown).Address)

        For Each Data In i1.Range("B2:" & i1.Range("B2").End(xlDown).Address)

            If CDate(Data.Value) > CDate(Match.Value) Then
                MsgBox (Data.Row)
                i1.Rows(Data.Row).Delete

            End If

        Next




    Next


End Sub

Why?

I can't understand it

If someone can help me

Upvotes: 0

Views: 199

Answers (1)

MarcinSzaleniec
MarcinSzaleniec

Reputation: 2256

This is simple. When you delete, lets say, fifth row, all rows below go up. So, the row that was sixth is fifth now. But your loop goes to the recent sixth (former seventh), jumping over this one that used to be sixth. Use FOR i = lastRow to 1 STEP -1 to delete rows upward. (ah, lastRow must be calculated before).

Upvotes: 1

Related Questions