CODEWITHSUNDEEP
excelvba
Florian
Florian

Reputation: 47

Reset a for loop when a condition is met vba

I want to reset a counter in a loop every time a condition is met.

Here my code : 

Set report = Workbooks("Rapports").Worksheets("Relevé_" & Client &  "_" & Datereport2)
For i = 1 To 11 'pas auto
    VM1 = report.Range("G" & i + 11).Value
    VM2 = report.Range("H" & i + 11).Value
     If VM1 = 0 And VM2 = 0 Then
     report.Rows(i + 11 & ":" & i + 11).Delete
     End If

     Next
end sub

When the row is deleted i want to restart i at 1, until no more cell = 0. Also, i dont want to delete rows where there is an empty cell.

Thank you !!

Upvotes: 0

Views: 3222

Answers (1)

Scott Craner
Scott Craner

Reputation: 152465

Two changes:

Set report = Workbooks("Rapports").Worksheets("Relevé_" & Client & "_" & Datereport2)
For i = 11 To 1 Step -1 'pas auto
    VM1 = report.Range("G" & i + 11).Value
    VM2 = report.Range("H" & i + 11).Value
     If VM1 = "0" And VM2 = "0" Then
     report.Rows(i + 11 & ":" & i + 11).Delete
     End If

     Next
End Sub

  1. Loop backwards. This will take care of any duplicates and will not need to run more than once.

  2. change 0 to "0" This will force it to find 0 and not empty cells.

Upvotes: 2

Related Questions