Delete set of cells based off of date

Xavier!3

New Member
Joined
Jul 5, 2010
Messages
9
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.
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,218
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.
Code:
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

New Member
Joined
Jul 5, 2010
Messages
9
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?
 
Last edited:

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,218
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Jul 5, 2010
Messages
9
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

MrExcel MVP
Joined
May 26, 2009
Messages
17,218
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
ABCDE
1JulyTargetDate6/24/2011
27/7/2010352
37/8/2010351
Sheet23
 

Watch MrExcel Video

Forum statistics

Threads
1,109,304
Messages
5,527,912
Members
409,792
Latest member
shawnash

This Week's Hot Topics

Top