Delete out of date rows

[Daph1990]

Hi,

I have been looking at this code for far too long, and the answer is probably very simple but right now nothing I am trying is working! So what I'm trying to do is delete all rows on sheet "All Contracts" if the date is before the date entered in cell A3 on "Steps & Instructions". The part I'm finding tricky is I only want to delete the rows if the following condition is met:

1) The date in column BV on sheet "All Contracts" is before the date entered in cell A3 on "Steps & Instructions" AND column CB on sheet All contracts is either "No" or blank.

For every other possible combination I would like to keep that corresponding row..i.e if both BV and CB are blank I'd like to keep them. This is the code I have so far and I have also attached the test file for viewing and to make things a little more clear:

Sub DeleteOldDates()'---- Macro to delete dates


Dim ws As Worksheet, ws2 As Worksheet
Dim LR As Long


Application.ScreenUpdating = 0


Set ws = Worksheets("Steps & Instructions")
Set ws2 = Worksheets("All Contracts")


LR = ws2.Range("BV" & Rows.Count).End(xlUp).Row
For i = LR To 2 Step -1
    renew = ws2.Cells(i, 80).Value
    tdate = ws2.Cells(i, 74).Value
    pdate = ws.Cells(3, 1).Value
    
    If (renew = "No" Or renew = "") And tdate <= pdate Then
        ws2.Cells(i, 1).EntireRow.Delete
    End If
Next i


    If pdate = "" Then
        MsgBox "No date entered in cell. Please try again.", vbExclamation, "Input Error!"
        Exit Sub
    End If
    
    MsgBox "Dates successfully deleted. Please proceed to Step 2.", vbInformation, "Successful!"
    
Application.ScreenUpdating = 1


End Sub

Thank you in advanced!

[mana]

If (renew = "No") + ((renew = "") * (tdate > 0)) * (tdate <= pdate) Then

[Daph1990]

Thank you for your response, but that doesn't do exactly what I require because it's still deleting everything that has "No" in Autorenew but I don't want it to do that. I only want it to delete Auto Renew "No" if the date in BV has expired. For example if the BV has a date of 01/02/2025 and Auto renew is set to "No" I want to keep this because it is still in date. So the only lines I want deleted are those where BV is a date in the past (before the date entered in cell A3 on the Steps & Instructions tab AND where auto renew is set to "No" or that cell is blank. The way the code is right now...it deletes the row in the example I've just given. Hope this makes sense

[mana]

If ((renew = "No") + (renew = "")) * (tdate > 0) * (tdate <= pdate) Then

[mikerickson]

How is the code in the OP failing? What rows isn't it deleting or what rows are being deleted erroneously?

I do note that your testing is case sensitive, so the presence of "NO" in column CB will result in no deletion.

[Daph1990]

Thank you @mana ...works great now!