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

[JBM91]

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

 

[Joe4]

You mention "database". This would be very easy with an actual database program like Microsoft Access (though many people use Excel as a database, it really isn't a database program, so it doesn't handle these things as easily as Access does.

If that is not an option for you, and you have to do it in Excel, I think it would be helpful if you could past some sample of what your data looks like. You can do that with the tools mentioned here: XL2BB - Excel Range to BBCode

We would really need to know:
- What your exact criteria is?
- What columns hold the data that we will be running this criteria against?
- Are the columns holding the dates formatted as "Date", "Number", or "Text"?

 

[JBM91]

You mention "database". This would be very easy with an actual database program like Microsoft Access (though many people use Excel as a database, it really isn't a database program, so it doesn't handle these things as easily as Access does.

If that is not an option for you, and you have to do it in Excel, I think it would be helpful if you could past some sample of what your data looks like. You can do that with the tools mentioned here: XL2BB - Excel Range to BBCode

We would really need to know:
- What your exact criteria is?
- What columns hold the data that we will be running this criteria against?
- Are the columns holding the dates formatted as "Date", "Number", or "Text"?

Hi Joe,

I'm definitely open to the idea of using Access!

The criteria would be that for an order to be archived, it must have a "Completed" value assigned to the "Value" - which is in column D - AND be older than two weeks from todays date. Row number 1 indicate weeks and have a General format.

I've tried using the XL2BB-tool you referenced to provide you with an idea of the layout and structure of the file:

Planlægning SCC - 2020 Implementation verTest.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1								Week	1	1	1	1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17							0
18
19
20
21
22
23
24									Thursday		Friday		Saturday		Sunday
25	Job/order no.	Confirmed shipping	Type	Status	Order type	Handler	Customer	Description	02-01-2020	Work hours 02-01-2020	03-01-2020	Work hours 03-01-2020	04-01-2020	Work hours 04-01-2020	05-01-2020	Work hours 05-01-2020
SCC HSS planning

Would you say that it's in any way possible? Thanks for taking the time to do this!

//Jbm

 

[Joe4]

Definitely possible, and not too hard to do in Access.

If you store the data in Access, you can easily create queries with Criteria on any fields that you want.
The only limitation on Access is the total database size, which can be 2 GB.
If you delete a lot of data, you will want to Compact & Repair the database regularly to "re-claim" the blank space after deleting large amounts of data.

What I would if I were you is import the data into Access, and see how much memory it uses.
If you see that you need to archive off data, I would do the following:
1. Create a query that identifies the records to be deleted, and then export the those records (to an Excel file, or text file, whatever you like)
2. Create a delete query that uses the exact same criteria as the first query, and delete those records with this (after first exporting them up above)
3. Compact & Repair the database to reclaim the memory.

There is lots of good information that can be found in Google searches, if you need information on them. If you go this route, and run into any issues/questions regarding details, post back here.

 

[JBM91]

The size of the database should not be any problem - the file in excel is 2,07 MB, so there's plenty of wiggle room to the 2 GB!

There is lots of good information that can be found in Google searches, if you need information on them. If you go this route, and run into any issues/questions regarding details, post back here.

I do have a few questions, if you don't mind!

When I import the file to Access, the layout of the file changes completely from the one I've posted above - and I'm just worried that if I create queries and start running them with the altered layout, said layout will too get implemented and override the one of my Excel-file?

When importing, it also appears as if I only get columns up until week 19.

I have more questions, but let's start with that - lol!

//Jbm

 

[Joe4]

How many columns to you have? I believe Access can handle up to 256 columns.
However, to use Access efficiently, your data should be normalized.
If you are adding new columns all the time, that may be a little problematic

For example, if you have your data structured so that you have multiple columns for each day of the week, or for each day/week, that makes the data hard to work with, as if you wanted to put criteria on the days/dates, you need to have criteria for each and every different day/date column (field)! That is a pain, whether it is in Access or Excel!

In a normalized data structure, instead of having different columns for each and every day/date, you would just have two columns:
- one for day (i.e. Sunday, Monday, etc)
- one for the value on that day

So instead of having different columns (fields) for each different day, you have multiple rows (records).
This makes querying very easy, as now you only have to query one field.

A well-designed database (with normalized tables) is usually easy to work with, but ones that are not normalized can turn into a nightmare!

 

[JBM91]

How many columns to you have? I believe Access can handle up to 256 columns.
However, to use Access efficiently, your data should be normalized.
If you are adding new columns all the time, that may be a little problematic

For example, if you have your data structured so that you have multiple columns for each day of the week, or for each day/week, that makes the data hard to work with, as if you wanted to put criteria on the days/dates, you need to have criteria for each and every different day/date column (field)! That is a pain, whether it is in Access or Excel!

In a normalized data structure, instead of having different columns for each and every day/date, you would just have two columns:
- one for day (i.e. Sunday, Monday, etc)
- one for the value on that day

So instead of having different columns (fields) for each different day, you have multiple rows (records).
This makes querying very easy, as now you only have to query one field.

A well-designed database (with normalized tables) is usually easy to work with, but ones that are not normalized can turn into a nightmare!

Yeah that's a problem. I have 738 columns in my file - some initial columns containing information about the specific job/order, and then 2 columns for every day of the year (which is fixed, so it's normalized in that sense).
So I guess it won't work with Access.

You also mentioned that a VBA-solution might be possible?

 

[Joe4]

and then 2 columns for every day of the year (which is fixed, so it's normalized in that sense).

That is definitely not normalized, and I think is going to present challenges regardless of whether you try to do this in Excel or Access.
If there are 2 columns for every day of the year, how exactly do you propose archiving the data? Are you going to delete some of the columns?
Because I don't think it is going to help a whole lot to delete the data in those columns, but leave those empty columns in you main table (not sure how you would delete rows, if each row contains all the days of the year).
If there any possibility of re-designing the structure of the data table, I would highly recommend doing that.

 

[JBM91]

If there any possibility of re-designing the structure of the data table, I would highly recommend doing that.

That's unfortunately not an option

Hmm, I'm thinking as an alternative to removing columns; would it be possible to instead make a macro to filter rows based on a specific text value in column D and then remove and archive those instead?

 

[Joe4]

I am a bit confused. If the rows have the values for every day of the year, why would you want to remove those? Don't you need those?
Or are you getting a fresh set of data rows every day (so new records do not update existing records, but you are just adding new rows/records every day?

If so, then I would recommend adding a calculated column to the end, which determines the last date updated on a row, and then you can filter on that field to get rid of old records.
Maybe that is what you had in mind with column D. How exactly does column D get updated for existing rows of data?