Looping is not working using For - Next Code

I have a macro using For & Next. The code works if the code is run multiple times. I would like the code to complete the process by pressing the macro button once.

Can someone look at the code and make the necessary changes PLEASE.

This macro deletes all dates in column "B" if less than column "F" less another 5 days.

Sub DeleteRows_Dates()

Dim x As Long
Dim LastRow As Integer

Range("B3").Select
LastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row

    For x = 3 To LastRow
     Debug.Print Cells(x, "B").Value
        If CDate(Cells(x, "B")) < CDate(Cells(x, "F") - Day(5)) Then
            Cells(x, "B").EntireRow.Delete
        End If
    Next x
   
End Sub
Deletes-Date2.xlsm
user2073Asked:
Who is Participating?
 
Martin LissConnect With a Mentor Older than dirtCommented:
You should run it backwards because deleting rows affects the For/Next index.

Sub DeleteRows_Dates()

Dim x As Long
Dim LastRow As Integer

Range("B3").Select
LastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row

    For x = LastRow to 3 Step -1
     Debug.Print Cells(x, "B").Value
        If CDate(Cells(x, "B")) < CDate(Cells(x, "F") - Day(5)) Then
            Cells(x, "B").EntireRow.Delete
        End If
    Next x
    
End Sub

Open in new window


And just in case you might ever have more than 32767 rows, you should make LastRow a Long.
0
 
user2073Author Commented:
Wow, you guys are quick. Yes the changes you make work THANKS.
0
 
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2012
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.