I have a spreadsheet in which records are entered potentially multiple times per day or not at all. It is attached.
Now the questions:
I want to be able to run a macro that will only keep 1 years worth of data based upon the start and end date. In my example if I ran the macro I would delete the first two rows. (Keep in mind the number of rows varies per day and the number per 12 month period will vary.)
So I am figuring that somehow I will have to determine the last day in the data (here it would be 2/1/15) then go back 12 months (so in the example it would be 2/2/14 but there is no data there so it would default to 2/3/14. Then the macro would have to delete the old rows (for this example the first two rows). Since I would want the macro to have to be initiated there might be a time that it would run and delete a month of data or the user might run it more frequently and only delete a day or two. Any suggestions?
As a similar question I want to change all my formulas to values for all but a set number of rows. So in my example I might want all the formulas starting with the row 1/31/15 up to row 2/1/14 to be copied and pasted as values. So once again the macro would have to determine the last row in the data set then move up the set number of rows (say 15) and then copy and paste > special > values all the data from this row up. (I know I will be copying and pasting numbers as well as formulas since every time this runs it will be picking rows that have already been processed. I figured this would be a lot easier than determining the rows that have formulas in them.) So, and this is where I need help with formatting I would:
1. Determine the last row: n
2. Subtract 14 from n to get the last row in the range to be "processed": n-14
3. Then select the range from row 8 (my first data row) to row n-14
4. Copy and paste special value in row 8 so that the entire copied range is pasted as values.
I think that the solution to either of the examples could be modified to get what I am wanting for the other, though I am not expert by any stretch of the imagination.
Thanks.
Steve
Bookmarks