Delete rows after a certain time period has passed

I would like rows to be deleted when a certain time frame is reached.
I would like the rows that are yet to meet this time period to be shifted up when the above rows are deleted.

I have found the following code:

Please Login or Register  to view this content.

Can it be improved in any way? I would like this to run automatically when the workbook is opened, rather than having to click run each time - is this possible?

Thanks

To have your macro initiate upon opening the workbook, place this into ThisWorkbook module:

Please Login or Register  to view this content.

That way, when you workbook opens, it will 'call' the macro delete_old and run it.

I haven't tested your macro delete_old. Are you having any issues with it ?

Originally Posted by Logit

To have your macro initiate upon opening the workbook, place this into ThisWorkbook module:

Please Login or Register  to view this content.

That way, when you workbook opens, it will 'call' the macro delete_old and run it.

I haven't tested your macro delete_old. Are you having any issues with it ?

Thanks for replying.

I already have other code in ThisWorkbook. Do I just paste the above code below?

Yes I think the macro is working OK, would just like to test from opening the file to confirm.

If you already have this sub in ThisWorkbook:

Please Login or Register  to view this content.

Then paste this line of code in that sub:

Please Login or Register  to view this content.

If this doesn't already exist :

Please Login or Register  to view this content.

Then paste this entire macro in ThisWorkbook:

Please Login or Register  to view this content.

You can test if the macro is being initiated when the workbook opens by inserting this:

Please Login or Register  to view this content.

here, in your original macro :

Please Login or Register  to view this content.

Post your code for review. If the workbook is not too large, having a copy posted here would be best. Remove any confidential info.

Originally Posted by Logit

Post your code for review. If the workbook is not too large, having a copy posted here would be best. Remove any confidential info.

Here's my code:

Please Login or Register  to view this content.

If nothing obvious is wrong above then I can upload the file.

Try this :

Paste this macro into a routine module (you may have already had it there to begin with ?):

Please Login or Register  to view this content.

Then in ThisWorkbook, paste this macro :

Please Login or Register  to view this content.

Now, if you want to test that the Workbook_Open macro is working (calling the delete_old macro) paste this line
at the top of the delete_old macro:

Please Login or Register  to view this content.

Save the workbook, close it, then open it again. The msgbox should show.

No need to paste the previous post into your reply, it takes up too much space on the servers.

Don't use REPLY WITH QUOTE.

See the attached project.

You will paste delete_old

anywhere in this sub:

Private Sub Workbook_Open()

End Sub


Excel (VBA) will search through the entire workbook project to find "delete_old". Once located it will run it.

In your Post #9, placing the call to the macro delete_old, where you put it, is fine. Some folks include the word ' Call '
prior to the macro name. Its simply another command telling Excel "Hey ... take notice of this line of code and execute it.
I want you to go look for this macro and run it."

Call delete_old

If you don't include the word Call, Excel still knows it has to go look for the macro and run it.

But I must need to state the sheet that I want delete_old to apply to somewhere? Otherwise it may just run on Sheet1 and I don't want it to start deleting rows on Sheet 1?

Either way, I can't get it to work. If I click somewhere in the delete_old code into Module1 and press play, it works and I get the message box "This macro is working". If I then paste "Call delete_old" in ThisWorkbook, save the spreadsheet and re-open, I do not automatically get the "This macro is working" message, and I do not know why!

Shall I attach my file here so you can have a look? thanks

Please attach.

Ok ... I changed the location of the call to macro delete_old.

It now displays when the workbook is opened and executes the macro as desired. (I believe that is what you wanted ?).

#1 - Please explain the use of the timer code located in the Workbook_Open module. #2 - When should the message box appear to notify the user of the 5 minute deadline ?
#3 - Are you wanting to autoclose the workbook ? An answer to all three questions (I know they seem the same, but I have an old mind ) is helpful.

Please Login or Register  to view this content.

Logit. Thank you for the above code - the delete_old now appears to be working as the "this macro is working" message box now appears when I open the file.

However, my timer code doesn't appear to be working as it did before (not sure if you moved some bits around or altered it?) - when I open the file my timer message box is stating "This file has been open for 0 minutes. You have 5 minutes to save before Excel closes." But my TimeInMinutes is set to 15, so it should only be displaying this message box after 10 mins with the 5 min warning, not straight away.

Furthermore, in answer to your questions:
1) (It's not my code so I'll do my best to answer).... the timer code has been implemented to auto close the workbook after a certain amount of time, to prevent a user from editing and then leaving the workbook open for long periods (due to inactivity or just due to taking too long).
2) When set to 15, the message box should appear after 10 mins, saying this file has been open for 10 mins, you have 5 mins to save before Excel closes".
3) Yes, I wish for the workbook to auto close after 15 mins, preferably save and close, rather than just close - not sure if this can be added in?

If you could help/advise that would be great. The one issue I was facing with the timer code was that when the workbook was opened, the code would auto run and in doing so seemed to lock down the task bars and any menu buttons, etc. It was suggested to me that this may be due to the loop, but I wasn't sure how to amend the code to prevent this whilst keeping its functionality - I need it to auto run when the workbook is opened, but not to lock down the menu's and task bars etc.

Many thanks

I also still have to ask regarding the delete_old code, how it knows to look for data in the "Archive" tab, considering this isn't specified anywhere and the code has been placed into Module1, rather than specific sheet code... thanks!

I understand the above part of your post regarding copying rows from Raw Data to the Archive tab, that wasn't what I was referring to.

I'm referring to the delete_old code, which is supposed to delete data from the Archive tab after a set period of time... this has nothing to do with the copying from Raw Data to Archive that you've mentioned above??? - I'm unsure how delete_old knows to delete rows from Archive...


Anyway, I am still receiving the message "This file has been open for 0 minutes. You have 5 minutes to save before Excel closes" as soon as I open the file... any idea why? I've used the code from your latest post above.

Thanks

in ThisWorkbook, the macro delete_old is 'called' here :

Please Login or Register  to view this content.

That tells Excel "go look through the workbook and find this macro "delete_old".

It locates the macro in Module 1 and knows what to look for and delete in this portion of the code :

Please Login or Register  to view this content.

I am not seeing the Warning Message here when I open the file. ???

Ignore my post about the warning message - that was my mistake, I have sorted it.

My question is, the big part of code you have put in red above, how does it know that the A2 mentioned is the A2 in Archive?? I don't see it relating to the Archive tab above unless I am completely mistaken and in which case I apologise.

I see what you are referring to now.

Copy and paste this macro - to replace the existing macro 'delete_old' :

Please Login or Register  to view this content.

Now, any entry in the ARCHIVE sheet that is older than 30 days will be deleted when the workbook is opened.

Perfect, works as expected as is just what I'm after. Thanks for your help and for sticking with this.

Glad to help. Cheers

Just one point which does seem to be an issue... the functionality to access toolbars/menu buttons etc still seems to be disabled or over-ran by something in the code when the macro is autorun on startup. Is this something that can be altered?