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!
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!