Delete set of cells based off of date

[Xavier!3]

Heres the issue gentleman i have a homemade low-tch calander inside of a excel worksheet. Low-tech to the point of one column is the date and the second column is a count of days all ultimatly counting down to zero. What i am trying to accomplish is at the beginning of every new day delete the 2 cells that corrospond to the day already past. Any suggestions? Whether it be macros or just a simple formula I'm overlooking im open to all suggestions or solutions.

 

[JoeMo]

Heres the issue gentleman i have a homemade low-tch calander inside of a excel worksheet. Low-tech to the point of one column is the date and the second column is a count of days all ultimatly counting down to zero. What i am trying to accomplish is at the beginning of every new day delete the 2 cells that corrospond to the day already past. Any suggestions? Whether it be macros or just a simple formula I'm overlooking im open to all suggestions or solutions.

Here's a macro you can adapt to your sheet (range you are using). I assume your dates are in column A.

Sub DelCells()
Dim dataRng As Range, delRng As Range
Set dataRng = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
For Each c In dataRng
    If CDate(c) < Date Then
        If delRng Is Nothing Then
            Set delRng = c.Resize(1, 2)
        Else
            Set delRng = Union(delRng, c.Resize(1, 2))
        End If
    End If
Next c
If delRng Is Nothing Then Exit Sub
delRng.Delete shift:=xlUp
        
End Sub

 

[Xavier!3]

appreciate ill give it a try tonight and get back with results.

 

[Xavier!3]

Feedback: The code given ended up deleting all of both columns A and B below is a diagram of what the spreadsheet looks like:
.....col A..............col B
1 July (merged)
2 07, July 2010 ....353
3 = A2+1 ............=B2-1
4 = A3 +1 ...........=B3-1
so forth
25 August (merged)
26 =A24+1 ..........=B24-1
so forth so on until the value of B=1
any revisions off of that? or am I missing something?

 

[JoeMo]

Feedback: The code given ended up deleting all of both columns A and B below is a diagram of what the spreadsheet looks like:
.....col A..............col B
1 July (merged)
2 07, July 2010 ....353
3 = A2+1 ............=B2-1
4 = A3 +1 ...........=B3-1
so forth
25 August (merged)
26 =A24+1 ..........=B24-1
so forth so on until the value of B=1
any revisions off of that? or am I missing something?

The way you have set up your sheet seems problematic. Cell A2 has today's date hard-coded as you've shown here, and all the cells below it use formulas to return the dates in them. That's fine until tomorrow when you want to delete cells A2 and B2 (if I understand your initial post). At that point, all the cells in column A that have dates derived from formulas in them will revert to #REF! errors because they all refer directly or indirectly to the the cell that was deleted. I will try to help you further if you can tell me exactly what you are trying to accomplish - there is probably a better way to do it.

 

[Xavier!3]

you are correct in saying that all dates are dependent on A2 i see how that could be problematic...the ultimate goal here is that at the beginning of each new day the day that just ended and the numbers in the same row but in column B are deleted and all text is moved up one position to keep the current date on top yet still underneath the current month headline. Understanding that this is probably way more complicated than need be any suggestions as to modifications to get a macro to fufill the idea are welcome the only thing that i think would need to stay would obviously be the dates and all values for column B (in respective rows to whatever day they "belong" to). Overall function would be to open the document and be able to see the date in any column and then how many days are left until june 24th 2011 in the column next to the dates.

 

[JoeMo]

you are correct in saying that all dates are dependent on A2 i see how that could be problematic...the ultimate goal here is that at the beginning of each new day the day that just ended and the numbers in the same row but in column B are deleted and all text is moved up one position to keep the current date on top yet still underneath the current month headline. Understanding that this is probably way more complicated than need be any suggestions as to modifications to get a macro to fufill the idea are welcome the only thing that i think would need to stay would obviously be the dates and all values for column B (in respective rows to whatever day they "belong" to). Overall function would be to open the document and be able to see the date in any column and then how many days are left until june 24th 2011 in the column next to the dates.

You don't need a macro to do this. Use the formulas below and pull them down from A3:B3 to as far as you want to go. When you open the workbook, the Merged Cells A1:B1 will always contain the name of the current month. Cell A2 will always show the current date. Cell E1 contains the target date you are counting down to. In this case I set it to 6/24/2011. Once the current date has reached the target date, only the current month and date will remain in A1:B2, the other cells in columns A & B will turn blank.

Excel Workbook
	A	B	C	D	E
1	July			TargetDate	6/24/2011
2	7/7/2010	352
3	7/8/2010	351
Sheet23

 

[Xavier!3]

hey sounds good ill give it a try today.