Delete old information from Excel 2003, VBA [Closed]

Report
Posts
14
Registration date
Sunday February 6, 2011
Status
Member
Last seen
September 1, 2011
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello,

My appreciation in advance for any responses to this.

I have a sheet which measures certain things on a daily basis, there is a "Sat", "Sun", "Mon" etc. tab. for each day. From here the information is collated by a series of lookups into a tab called the "databank" so that the whole weeks worth of information is stored in once place.

I have this part working well, what I am having difficulty with is the next part.

I need to collate this weekly information into another tab called the "currentyear" and for this i'd like a macro. What I have already takes the "databank" information, pastes it through and then removes (using autofilter) any gaps in the info. I am left with a bunch of rows containing all the information with no empty rows until the end of the information (each day there are 40 rows for input, and if only 20 rows are used i only want those 20 rows in my currentyear).

After a while this tab gets very full and in truth I only need to keep the information here for 13 weeks. I would therefore like to add to my macro that at the point of bringing the new weeks information to the currentyear, take any week more than 13 weeks old, out.

The information does contain a number of things that indicate it's age. Column A has the date from whichever day the information relates to, Column B is the week number (from a simple Weeknum(A*) formula) so i guess it could be an "if the date in the column is older than 13 weeks before the most recent date in the list" OR a "look at the highest week number, delete any row that highest week num - 13 = >1" - - - or something like that.

I'd be thrilled to hear some of the suggestions, thanks in advance!


1 reply

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Could you post a sample workbook with some sample data at some file sharing site and post back the link. If you could use that workbook to explain what you seek, it would be easier to see what can be done

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!