Managing list using dates!

[HammerJoe]

Hi,

Here is an interesting problem.

I have an archive sheet that is used to store info at the beggining of
every week.

Right now each week and at beggining of every month the weekly results
and/or monthly are added to the first empty row in the sheet.

Column A identifies the date range.
For weekly results it shows "21Jul-26Jul" by using this code

..Range("A" & LastRow).Value =
Format(Sheets("Settings").Range("E24").Value, "ddmmm") + "-" +
Format(Sheets("Settings").Range("E25").Value, "ddmmm")

where E24 has the date of the beggining of the week (2008/07/21) and
E25 the end of the week (2008/07/26)

For the Monthly results it shows "JUL-08" (month and year) using this
code :

..Range("A" & LastRow).Value =
Format(Sheets("Settings").Range("E24").Value, "mmm-yyyy")

Pretty straightforward and simple.
Now I want to complicate things and add daily results to this sheet as
well.
This daily number would also only be added at the end of week.
Column A would have full date (2008-JUL-21).

Thats the easy part.

Where it gets complicated is that I want to remove all the daily
results rows if the date is older than a user defined number of weeks
but keep the Weekly and Monthly results.

For example if its defined that results older than four weeks should
be deleted, then the macro would have to find anything older than
Jun-25 and remove the daily rsult rows that are older than four weeks.

My two problems with this:

- Does Excel have a quick and easy way to find the row that matches
the date instead of using a loop to go thru all the rows from the
bottom up until it finds the date?

- How to delete the rows that are daily results and leave the weekly
and monthly ones because I want to keep those?

Thanks.

 

[Barb Reinhardt]

How many rows of dates do you have? 1000's? It shouldn't take that long to
execute.

As for deleting the days and not the months, I'm thinking you'll have to
test for formatting, because the value you have for each month is probably
really a date formatted to look like a month.

I'd recommend that you define a range to loop through and if you want to
delete a row, add it to another range. Use Union there. Once you've found
all the rows to delete, test for the existance of the second range and if it
exists, delete it.

 

[HammerJoe]

How many rows of dates do you have?  1000's?   It shouldn't take thatlong to
execute.

You mean in a do loop?
I dont know, I guess the problem that I want to avoid because I've
experienced is that if there are other workbooks open that also use
macros, for some reason it slows down running macros that use some
kind of loop.
I am not sure why that is, I have a feeling it slows down if it has to
read info from cells.
I try to stay away as much as I can.

As for deleting the days and not the months, I'm thinking you'll have to
test for formatting, because the value you have for each month is probably
really a date formatted to look like a month.  

Test for formatting??
It's probably the best option... I will try that.

I'd recommend that you define a range to loop through and if you want to
delete a row, add it to another range.  Use Union there.  Once you'vefound
all the rows to delete, test for the existance of the second range and ifit
exists, delete it.  

I am not familiar with Union, but I not sure what you are proposing
with the two ranges?
Can you be a bit more clear please?
Thanks for the help.

 

[HammerJoe]

Here is what I came up with that seems to be working:

Dim ArchiveDate As Date
Dim LastRow as Long
'Calculate date to delete rows, E25=This week saturday date,
J16=Number of weeks to go back
ArchiveDate = ThisWorkbook.Sheets("Settings").Range("E25").Value - _
(ThisWorkbook.Sheets("Settings").Range("J16").Value * 7)
LastRow = Sheets("Archive").Cells(Sheets("Archive").Rows.Count,
"A").End(xlUp).Row + 1
With Sheets("Archive")
'Data starts at row 6
Do While LastRow > 5
If .Range("A" & LastRow).NumberFormat = "d-mmm-yy" Then
If .Range("A" & LastRow).Value <= ArchiveDate Then
MsgBox ("Got a hit")
Rows(LastRow).Delete
End If
End If
LastRow = LastRow - 1
Loop
End With

What do you think?

Thanks for the sugestion to comapre the format, that is the answer to
this problem.
Cheers.