• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Auto delete after 30 days...

J

James Perry

Member
How can i get excel to auto delete any cell in a worksheet is older than 30 days. This will keep only cells with data under 30 days in the worksheet.

4 instance i'm doing a survey 4 a new product.
I have a database of 2000 names&mobile numbers in a worksheet.
New names r added everyday.
There is a routine follow-up with the party periodically.
Parties that response positively within 30 days r transferred to a another worksheet.
And parties that r not interested must not appear in the worksheet after 30 days.
Thank u.
 
In this case it's better not to delete the data instead you can add helper column with active and non active user.
putting if condition and see final data in pivot table in another sheet by choosing only active user.
for better understanding if you could share your worksheet with sample data .

Brij A
 
Hi James,

you can do this by adding the Datetime(=Now) comments to each cell (Column with Names etc.,) you edit in the workbook. Everytime you edit a cell, the comment gets updated with the current datetime. Have a button or a routine to run periodically when you want to check the cells and loop through each cell in a defined range to find the comment value. If comment value is more than 30 days, delete the cell value.

Have this code in your worksheet module to add the comment on everycell you edit on and a button routine to check and delete the value if the day difference is more than 30.

HTML: 
'Routine to check if the last edited datetime of the cell is more than 30days, if so delete the value of the cell
Private Sub CommandButton1_Click()
Dim cl As Range
Dim commtext As String
For Each cl In Cells.Range("A1:F8")
    If Not cl.comment Is Nothing Then
        commtext = cl.comment.Text
        'If (Day(commtext) - Day(Now)) > 30 Then cl.Text = ""
          If Application.WorksheetFunction.Days360(commtext, Now) > 30 Then cl.Value = ""
    End If
Next
End Sub
 
'Event to add the current datetime comment on the cell getting edited.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim comment As String
    comment = Now
    If Target.Column = 1 Then 'Check if the cell is of A column
        Target.Cells.NoteText comment
        Application.DisplayCommentIndicator = xlCommentIndicatorOnly
  End If
End Sub
 
James

There is no data limit set on the forums for asking a question, you do not have to use as if you where texting from your phone :)
 
@b(ut)ob(ut)hc
Hi, my friend!
Good afternoon, ... ok, good it's just an expression... I'm like in the Penines, it's raining since a week ago and it doesn't stop.
I was wondering who was still using the last functioning Nokia 1100 in the world... and I discovered him here: James!
Regards!
 
Good day my friend

I hope the weather does not revisit Argentina with more floods, I watched the last one the news, not good
 
Good day SirJB7

People have the misguided image of the UKGB being a wet place, always raining, but this is an image made by Hollywood film producers of third rate B movies who always put scenes of London in the rain. London has less annual rain than New York, when you see the flooding in Argentina, India, Bangladesh or the European Continent then we realise just how small our so called floods are.
 
Thank u guys.
I am now looking at a simple VBA solution like the following.
Since i am a novice at VBA; can anybody help complete it.
Thank u in advance.

select range
for each row, if date < today() -30
delete row
 
Hi, James Perry!
Lohithsriram's solution wouldn't be as easier as your posted skeleton snippet but it's fully working. Have you given it a try?
Regards!
 
You must log in or register to reply here.
Back
Top