# Delete set of cells based off of date

#### Xavier!3

##### New Member
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.

### 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
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
appreciate ill give it a try tonight and get back with results.

#### Xavier!3

##### New Member
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

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
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
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

#### Xavier!3

##### New Member
hey sounds good ill give it a try today.

Replies
2
Views
266
Replies
1
Views
240
Replies
1
Views
1K
Replies
0
Views
389
Replies
3
Views
204