Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Managing list using dates!
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Managing list using dates!
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. -- HTH, Barb Reinhardt " wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Managing list using dates!
On Aug 2, 11:31*pm, Barb Reinhardt
wrote: How many rows of dates do you have? *1000's? * It shouldn't take that long 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've found all the rows to delete, test for the existance of the second range and if it 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Managing list using dates!
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").Valu e * 7) LastRow = Sheets("Archive").Cells(Sheets("Archive").Rows.Cou nt, "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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I generate a list of random dates from dates I specify | Excel Worksheet Functions | |||
How to filter out all the partial dates from a big list of dates? | Excel Discussion (Misc queries) | |||
Counting dates, within a list of dates | Excel Worksheet Functions | |||
Need to pull current dates from list w/many dates | Excel Discussion (Misc queries) | |||
need to convert list of dates to count no. of dates by week | Excel Worksheet Functions |