I have tested it out with a couple hundred values, and it works for me.No, same thing....date is still showing
Private Sub Workbook_Open()
Dim cell As Range
Dim cl As Long
Dim rw As Long
MsgBox "Code is running", vbOKOnly
Application.ScreenUpdating = False
' Loop through columns D to AA
For cl = 4 To 27
' Loop through odd number rows from to 3 to 59
For rw = 3 To 59 Step 2
' Check dates in cells to see they are more than a year old
If (Cells(rw, cl) > 0) And (Date > (Cells(rw, cl) + 365)) Then
' Clear date from cell
Cells(rw, cl).ClearContents
End If
Next rw
Next cl
Application.ScreenUpdating = True
End Sub
=ISNUMBER(D7)
I have tested it out with a couple hundred values, and it works for me.
To confirm:
- The code must be in the "ThisWorkbook" module
- VBA code MUST be enabled
- There is only one sheet in the workbook
- Dates entered must be more than one year old and entered into odd numbered rows (between 3 and 59) in columns D:AA
- Dates must be entered as valid dates, and not as text
Two things to test:
1. Amend the code to add a MsgBox row to the code, like this:
When you open your workbook, you should get the message "Code is running". If not, VBA has not been enabled and the code is not running.VBA Code:Private Sub Workbook_Open() Dim cell As Range Dim cl As Long Dim rw As Long MsgBox "Code is running", vbOKOnly Application.ScreenUpdating = False ' Loop through columns D to AA For cl = 4 To 27 ' Loop through odd number rows from to 3 to 59 For rw = 3 To 59 Step 2 ' Check dates in cells to see they are more than a year old If (Cells(rw, cl) > 0) And (Date > (Cells(rw, cl) + 365)) Then ' Clear date from cell Cells(rw, cl).ClearContents End If Next rw Next cl Application.ScreenUpdating = True End Sub
2. Identify a cell that should be cleared, but is not.
What is that cell address?
What is the exact entry in that cell?
Let's say that it is cell D7. Enter this formula in any blank cell and tell me what it returns (for whatever cell is not working correctly):
Excel Formula:=ISNUMBER(D7)
Do you get the Message Box pop-up?Yes, that is what I see. I click on Enable, but the dates still don't disappear
If Macros are enabled, and you are not getting the "Code is running" message, it can really only mean one of two things:
1. You did not place the code in the correct module (needs to be in the "ThisWorkbook" module of this particular file.
- or -
2. You changed something about the code, so it is not being recognized as Event procedure code, and won't run automatically. Did you copy and paste the code, exactly as I have written it?
Sub Test()
MsgBox "This is a test", vbOKOnly
End Sub