Delete Rows With Date 7 Days Old

L

LSM1604

New Member
Joined
Jan 8, 2010
Messages
49
Hi,

I was wondering if somebody could help me with a little bit of code?

When the sheet is opened, I would like some code to search down column A, starting from cell "A5" and delete all rows which contain a date which is greater than 7 days ago.

The dates are in order from the most recent being at the top.

For example:
Today is 29/01/2010, I would like all rows which have a cell in column A containing Today-7() to be deleted. 28/01/2010 to stay. 19/01/2010, delete row.


Hope that makes sense?

Cheers,

LSM
 
Sort by date Sort by votes
Hi all,

I have managed to solve the problem:

Code: 
Private Sub Worksheet_Activate()
Application.ScreenUpdating = False
ActiveSheet.Unprotect Password:=""
    Range("A5").Select
    Do While ActiveCell <> ""
    If ActiveCell.Value < Now() - 7 Then
    ActiveCell.EntireRow.Delete
    ActiveCell.Offset(-1, 0).Activate
    Else
    ActiveCell.Offset(1, 0).Activate
    End If
    Loop
Range("A1").Select
ActiveSheet.Protect Password:="", DrawingObjects:=True, Contents:=True, Scenarios:=True, UserInterfaceOnly:=True
ActiveSheet.EnableSelection = xlUnlockedCells
Application.ScreenUpdating = True
End Sub


LSM
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Get average balance of date range broken down by month
2
Replies
11
Views
381
Tetra201
Tetra201
K
  • Question Question
Best Way to automatically delete backup files using vba
Replies
4
Views
155
komobu
K
M
  • Question Question
VBA CountIf Function
Replies
5
Views
276
Alex Blakenburg
A
M
  • Solved
Marco to delete rows with a future date
2
Replies
14
Views
492
MARK858
MARK858
T
  • Question Question
IF and Match and Input value Issue.
Replies
2
Views
130
timuk26
T

Share this page

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