Solved Excel Auto Clear Cell

November 21, 2013 at 08:25:21
Specs: Windows 7
I have a spread sheet that contains several different dates. How do i automatically clear the dates entered into the cells after the dates entered have expired?

See More: Excel Auto Clear Cell

Report •


✔ Best Answer
November 23, 2013 at 08:42:26
I need the cell to auto clear

Have no idea why you need it to auto clear, when typing in the new date, over the existing date, does the same thing, but try this marco:

Sub DeleteDates()
    Dim x As Range
    For Each x In Selection
        If IsDate(x) Then
            If DateDiff("d", x, Date) >= 60 Then x.ClearContents
            End If
    Next x
End Sub

Tested very lightly and seems to work.
You have to select the cell or range of cells.

MIKE

http://www.skeptic.com/



#1
November 21, 2013 at 10:42:05
Here's a Macro that should do it:

Sub DeleteDates()
    Dim x As Range
    For Each x In Selection
        If IsDate(x) Then x.ClearContents
    Next x
End Sub

Will only clear Date Cells

See how that works for you.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#2
November 21, 2013 at 10:53:29
Is there a way to extend the delete date for 2 months?

Report •

#3
November 21, 2013 at 11:01:46
The macro deleted all the dates

Report •

Related Solutions

#4
November 21, 2013 at 12:33:38
What do you mean by "extend the delete date for 2 months"?

Are you trying to only delete dates that are within a certain range? Please be specific with your answer, since the code has to be specific enough to meet your requirements.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
November 21, 2013 at 12:49:17
I want the dates to auto delete two months after the date expires.

Report •

#6
November 21, 2013 at 14:49:01
Imagine that you are one of us out here on the interweb where we can't see your spreadsheet. You really need to supply some details about how you are using your workbook if you want use to help you.

re: "I want the dates to auto delete two months after the date expires."

How will the code know when the date expires? Is there another date that the dates to be deleted will be compared to? Will there be a value in a cell that the code can look at to determine when a date expired? Neither Excel nor VBA can think on their own or read minds. There has to be something that will indicate to the code that a particular date should be deleted. You need to tell us what that "something" is.

If you can't explain in words how the code will know when the dates should be deleted, please post some example data and we'll see what we can do. If you click on the following line, you'll find instructions on how to post example data in this forum.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#7
November 22, 2013 at 08:05:19
Looks like my work site blocks it. I have a table with five different tasks (Columns). Each column has five rows. Each cell has start and end dates (i.e. 1/2/2013 and 1/10/2013). I would like the cell to automaticly clear once the date is two months passed? That way all I have to do is enter a new date and not have to go through each cell and delete the date. Hopefully this helps?

message edited by CJFuent


Report •

#8
November 22, 2013 at 09:05:47
I have a table with five different tasks (Columns). Each column has five rows. Each cell has start and end dates (i.e. 1/2/2013 and 1/10/2013). I would like the cell to automatically clear once the date is two months passed?

OK, so we have two dates in Column B, Row 2 & 3 labeled Start & End, so your data looks something like:

     A            B
1)             Task One
2)Start Date  01/02/2013
3)End Date    01/10/2013
4)
5)
6)

That way all I have to do is enter a new date and not have to go through each cell and delete the date.

Since I have used up what little VBA skills I have, instead of Deleting the contents,
how about we simply turn the Cell RED?

That should catch your attention, then you simply enter a new date, just type it in over the old date, no need to delete the contents.

Try this:

Conditional Formatting 2007

1) Select your cell or range of cells: Select cell B3
2) On the ribbon click Conditional Formatting
3) Click on New Rules, it’s near the bottom of the dialog box.
4) Click Use Formula to determine which cells to format.
5) Enter the formula:

=TODAY()-$B$3>=60

6) Click on the Format button
7) Select the Fill Tab
8) Select a RED color
9) Click OK
10) Click OK

See how that works for you.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#9
November 22, 2013 at 09:08:20
I have that now but that just turns it red. I need the cell to auto clear.
Thank you

message edited by CJFuent


Report •

#10
November 23, 2013 at 08:42:26
✔ Best Answer
I need the cell to auto clear

Have no idea why you need it to auto clear, when typing in the new date, over the existing date, does the same thing, but try this marco:

Sub DeleteDates()
    Dim x As Range
    For Each x In Selection
        If IsDate(x) Then
            If DateDiff("d", x, Date) >= 60 Then x.ClearContents
            End If
    Next x
End Sub

Tested very lightly and seems to work.
You have to select the cell or range of cells.

MIKE

http://www.skeptic.com/


Report •


Ask Question