PDA

View Full Version : Delete out of date rows



Daph1990
11-02-2017, 10:26 AM
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
11-02-2017, 07:18 PM
If (renew = "No") + ((renew = "") * (tdate > 0)) * (tdate <= pdate) Then

Daph1990
11-03-2017, 03:44 AM
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
11-03-2017, 04:35 AM
If ((renew = "No") + (renew = "")) * (tdate > 0) * (tdate <= pdate) Then

mikerickson
11-03-2017, 07:03 AM
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
11-03-2017, 08:01 AM
Thank you @mana ...works great now!