For/Next Is Not Looping - Only Deletes First Instance

  • Thread starter Thread starter Safari
  • Start date Start date
S

Safari

This code compares dates in range A5:A500 if any of them are greater
than the value in A1 it deletes that row.
It only deletes the first row that meets this criteria, then it
stops. If I copy the block of code twice, it works on the first two
rows. How can I make this loop?

Dim c As Range, compdate As Date
compdate = Range("A1").Value

If IsDate(compdate) Then
For Each c In Range("A5:A500", Range("K65536").End(xlUp))
If IsDate(c.Value) And c.Value > compdate Then
c.EntireRow.Delete
End If
Next

End If
 
Hi Safari

Starting at the bottom and moving backwards will help. Something like
this should do the trick.

Take Care

Marcus

Sub DelGreaterThan()

Dim i As Integer
Dim CompDate As Date
CompDate = Range("A1").Value

For i = 500 To 5 Step -1
If Range("A" & i).Value > CompDate Then
Range("A" & i).EntireRow.Delete
End If
Next i

End Sub
 
Hi Safari

Starting at the bottom and moving backwards will help.  Something like
this should do the trick.

Take Care

Marcus

Sub DelGreaterThan()

Dim i As Integer
Dim CompDate As Date
CompDate = Range("A1").Value

For i = 500 To 5 Step -1
    If Range("A" & i).Value > CompDate Then
        Range("A" & i).EntireRow.Delete
    End If
Next i

End Sub
Click to expand...

Brilliant.

Thanks so much Marcus!!!!
 
Back
Top