I need help writing a VBA code for 2007 Excel. I have a document that
multiple people have access to. Within this document there are dates and data
next to those dates. I need all dates deleted upon being opened if it's
greater than 6mos from the date that it is opened. The dates are in columns
E, H and K and the data for each date is directly to the right (F, I and L).
The information goes all the way down from row 5 to row 450. I hope this
makes sense.

This code should do it for you. It is Workbook event code, so it needs to go
into the 'ThisWorkbook' object's code module. To get it there,
Make a copy of your workbook to test with.
Open the test copy of the workbook.
Press [Alt]+[F11] to open the VB Editor (VBE). In the VBE press [Ctrl]+[R]
to make sure the Project Explorer window is open.
The Project Explorer window with have a title like 'Project - VBAProject'
and in it will be a list of "Microsoft Excel Objects"; expand that list if
you have to. It will show all the worksheets in the book and at the bottom
of the list will be the "ThisWorkbook" object.
Double-click on the ThisWorkbook object to see its code module, which will
probably have no code at all in it.
Copy the code below, paste it into the ThisWorkbook code module, edit the
sheet name to be the sheet you need to clean up. Save and close the
workbook. Open the workbook to test it.

Private Sub Workbook_Open()
Dim myWS As Worksheet
Dim myRange As Range
Dim anyCell As Range
Dim testDate As Date

'change sheet name as required
Set myWS = ThisWorkbook.Worksheets("Sheet1")

testDate = Now() + 180

Set myRange = myWS.Range("E5:E450")
For Each anyCell In myRange
If anyCell testDate Then
anyCell.ClearContents
anyCell.Offset(0, 1).ClearContents
End If
Next

Set myRange = myWS.Range("H5:H450")
For Each anyCell In myRange
If anyCell testDate Then
anyCell.ClearContents
anyCell.Offset(0, 1).ClearContents
End If
Next

Set myRange = myWS.Range("K5:K450")
For Each anyCell In myRange
If anyCell testDate Then
anyCell.ClearContents
anyCell.Offset(0, 1).ClearContents
End If
Next
'housekeeping
Set myRange = Nothing
Set myWS = Nothing
End Sub


"JAbels001" wrote:

I need help writing a VBA code for 2007 Excel. I have a document that
multiple people have access to. Within this document there are dates and data
next to those dates. I need all dates deleted upon being opened if it's
greater than 6mos from the date that it is opened. The dates are in columns
E, H and K and the data for each date is directly to the right (F, I and L).
The information goes all the way down from row 5 to row 450. I hope this
makes sense.

Thanks for responding.. I followed your steps to a "T" and when I opened the
file it did run the code and worked perfectly from what I could tell; however
I then placed some dates that are greater than 180 days (01/10/2009 for
example), saved and re-opened but it didn't delete that date... Any ideas?

"JLatham" wrote:

This code should do it for you. It is Workbook event code, so it needs to go
into the 'ThisWorkbook' object's code module. To get it there,
Make a copy of your workbook to test with.
Open the test copy of the workbook.
Press [Alt]+[F11] to open the VB Editor (VBE). In the VBE press [Ctrl]+[R]
to make sure the Project Explorer window is open.
The Project Explorer window with have a title like 'Project - VBAProject'
and in it will be a list of "Microsoft Excel Objects"; expand that list if
you have to. It will show all the worksheets in the book and at the bottom
of the list will be the "ThisWorkbook" object.
Double-click on the ThisWorkbook object to see its code module, which will
probably have no code at all in it.
Copy the code below, paste it into the ThisWorkbook code module, edit the
sheet name to be the sheet you need to clean up. Save and close the
workbook. Open the workbook to test it.

Private Sub Workbook_Open()
Dim myWS As Worksheet
Dim myRange As Range
Dim anyCell As Range
Dim testDate As Date

'change sheet name as required
Set myWS = ThisWorkbook.Worksheets("Sheet1")

testDate = Now() + 180

Set myRange = myWS.Range("E5:E450")
For Each anyCell In myRange
If anyCell testDate Then
anyCell.ClearContents
anyCell.Offset(0, 1).ClearContents
End If
Next

Set myRange = myWS.Range("H5:H450")
For Each anyCell In myRange
If anyCell testDate Then
anyCell.ClearContents
anyCell.Offset(0, 1).ClearContents
End If
Next

Set myRange = myWS.Range("K5:K450")
For Each anyCell In myRange
If anyCell testDate Then
anyCell.ClearContents
anyCell.Offset(0, 1).ClearContents
End If
Next
'housekeeping
Set myRange = Nothing
Set myWS = Nothing
End Sub


"JAbels001" wrote:

I need help writing a VBA code for 2007 Excel. I have a document that
multiple people have access to. Within this document there are dates and data
next to those dates. I need all dates deleted upon being opened if it's
greater than 6mos from the date that it is opened. The dates are in columns
E, H and K and the data for each date is directly to the right (F, I and L).
The information goes all the way down from row 5 to row 450. I hope this
makes sense.