VBA - removing rows/data older than x days/weeks

JBM91

New Member
Joined
Oct 22, 2019
Messages
25
Office Version
  1. 365
Platform
  1. Windows
Hi experts,

You've been very helpful in the past, so I figured I'd ask you for help again!

I have a database with columns split into days and weeks for the year, and rows representing jobs / orders.
Cells will then be populated with relevant data for the various jobs / orders in a given day.

The problem is that 5-6 months into the year, the file starts to get very large - and as such, slow to work with.

I was therefore hoping to make a macro where the user can remove/allocate "old" data - i.e. data that is older than, say, two weeks from today - to a separate file, where it can be archived. (That way the user can always jump into the archive-file, should they feel the need to backtrack something).

.. here's the trick though! It needs to only remove rows when a cell meets a specific criteria that indicates that the job/order is no longer "alive".

I have very little experience with VBA, and any suggestions and help you may be able to provide will be much appreciated!

//Jbm
 
Each row doesn't have data populated in each column - far from it! Typically, a row will only have data in a few columns.

If a row has a specific text value assigned to it in the D-column, it's essentially no longer active. So realistically, those rows only take up unnecessary space in the file.
 
Upvote 0

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
If a row has a specific text value assigned to it in the D-column, it's essentially no longer active. So realistically, those rows only take up unnecessary space in the file.
Sounds like then you would be able to place filters/criteria on row D, to decide which rows to keep and which ones to archive.
So it sounds like then you may be able to use Access to do this. For the records that need archiving, then deleting, you would have Criteria like this on column D:
<>""
 
Upvote 0
But will Access not alter the layout of my excel file? And how could I go about then doing the archiving and deleting once I've defined my filter?
Again, thanks for taking the time!
 
Upvote 0
But will Access not alter the layout of my excel file? And how could I go about then doing the archiving and deleting once I've defined my filter?
Again, thanks for taking the time!
I am assuming that you would import the data into Access, so you would maintain everything in there (Access instead of Excel).
Is that an option, or do you want to deal strictly with Excel?
If Excel, you can use Filters and/or Advanced Filters to separate the rows to keep versus the rows to archive.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top