Clearing contents based on one cells date

[bluemonster]

I have a spreadsheet that lists employees sickness record, a row for each occasion of absence. The first column states the first date of sickness. I want a button to be able to update, this would consist of clearing rows that have a starting date that was more than a year ago. Can anyone help?? If more information is needed please let me know and I will try and provide more. Thanks everyone

 

[bluemonster]

Please can someone help with this????

 

[Tazguy37]

Put this code in a standard module and assign it to a button:

Sub delOldRecords()
Dim i As Long

For i = Range("A65536").End(xlUp).Row To 1 Step -1
    If IsDate(Cells(i, 1).Value) Then
        If DateDiff("d", Cells(i, 1), Date) > 365 Then Rows(i).Delete
    End If
Next i

End Sub

Press Alt-F11 to open the VBE.
Press Control-R to open the Project Explorer.
Click "Microsoft Excel Objects" for the file you're working on.
Select Insert, Module from the menu.
Open the Code pane with F7.
Paste the above code in.
Press Alt-Q to close the VBE and return to Excel.

Hope that helps!