Delete entire row if cell date is older than 30 days from one of 3 locations.

Wombat62

New Member
Joined
Sep 30, 2009
Messages
1
I have an excel worksheet that is set to update the next cells with a date if a value was changed in a cell.
This then copies the entire row to another worksheet called Changes if any value in K, M changes

In Cell K is the data and in L is the date it was last amended.
The same in Cells M & N then again in O and P shows when the value equal completed or wholesale.

What I am wanting is to add in backward order so if Cell P date is older than 30 days delete row but if the cell is blank then check the date in cell N and if this cell is blank then check cell L.

If Application.Intersect(Target, Union(Range("k:k"), Range("m:m"))) Is Nothing Then Exit Sub
If Target <> "COMPLETED" And Target <> "WHOLESALE" Then
Target.Offset(0, 1) = Date
Target.Offset(0, 1).NumberFormat = "dd-mm-yy"
End If
Set wsCopy = ActiveSheet
Set wsPaste = ActiveWorkbook.Worksheets("CHANGES")
Set rngCopy = wsCopy.Range("a" & Target.Row & ":q" & Target.Row)
rngCopy.Select
Set rngPaste = wsPaste.Range("a" & wsPaste.Range("a" & Rows.Count).End(xlUp).Row + 1)

rngCopy.Copy
rngPaste.PasteSpecial
Application.CutCopyMode = False
Set rngCopy = Nothing
Set rngPaste = Nothing
Set wsCopy = Nothing
Set wsPaste = Nothing

Public Sub Delete()
'In Changes worksheet
lastrow = Cells(Cells.Rows.Count, "P").End(xlUp).Row
For x = lastrow To 3 Step -1
If IsEmpty(Cells(x, 16).Value <= Date - 30) Then
Cells(x, 16).EntireRow.Delete
ElseIf IsEmpty(Cells(x, 14).Value <= Date - 30) Then
Cells(x, 14).EntireRow.Delete
ElseIf IsEmpty(Cells(x, 12).Value <= Date - 30) Then
Cells(x, 12).EntireRow.Delete
End If
Next
End Sub
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Forum statistics

Threads
1,223,896
Messages
6,175,260
Members
452,627
Latest member
KitkatToby

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top