VBA Code to archive or delete and change data

[mdot218]

Hi

I am creating an attendance log for work, and want to see if my 2 requests can work.

1) After 365 days has elapsed, I want the row, containing that date to either be archived or deleted. In my example attached, I have the in column B, with more data in other columns, such has someone's name, occasion etc
2) The second part: If someone has multiple entries, can the next date (once the previous one was archived or deleted), change the occasion number accordingly, say from a 2 to a 1?

attached is an example worksheet.

Many thanks in advance.

[Greg M]

Hi there,

Take a look at the attached workbook and see if it does what you need.

The code used for deleting outdated records is as follows:

Please Login or Register  to view this content.

The highlighted values may be altered to suit your own requirements.


Rows which contain entries older than 365 days will be automatically highlighted for deletion, and all such rows can be deleted by clicking on the "Delete Old Records" button.

Note that the text "To Be Deleted" (as displayed on the worksheet) is specified as the value of the workbook-level Defined Name "sDELETE". This Defined Name is used in Formulas, for Conditional Formatting, and also in the VBA Code. Specifying the text value in this way means that your own preferred text (e.g. "For Deletion", "Time-Expired" etc.) can be specified as the value of the Defined Name "sDELETE", and all Formulas, Conditional Formatting etc. will remain unaffected by the change.

Hope this helps - please let me know how you get on.

Regards,

Greg M

[mdot218]

Hi Greg

Firstly, thank you for the explanation, which I find very valuable, as it does help me learn as I go.

Secondly, this is what I am after. I have tested it, and found it to work well. I also dropped it into my existing workbook, made some changes, and works well.

regards

Darren

[Greg M]

Hi again Darren,

Many thanks for your feedback and also for the Reputation increase - much appreciated

You're welcome - I'm very pleased that I was able to help.

Best regards,

Greg M