MACRO TO CLEAR CELL CONTENTS

  • Thread starter Thread starter Jay Dean
  • Start date Start date
J

Jay Dean

I have an Excel sheet on which:

1. columns B and D contain the same dates
down each column.

2. From column E and up contain data

** I am looking for a macro that will loop through
columns B and D. If it finds any row on which the
dates in B and D do not fall within the last month's
dates then
- it should clear those dates in B and D, and also
- clear all data on that row starting from Column E and
up.
- Where B and D do not contain any dates it
should do nothing.

I would appreciate your assistance very
much. Thanks.
Jay Dean
 
Jay

Often a macro is an overkill. I use frequently other columns and a 'flag'
to determine rows to delete, eg. In a blank column, say F1)

=IF(OR(B1<NOW()-30,C1<NOW()-30),"x","")

This will put an x in the column where the dates in B1 OR C1 are less then
today - 30 days. You can the simply filter this and delete the rows.

Someone else will probably come up with a code solution, but just another
route
 
Sub ClearOldDates()
Dim oCell As Range

For Each oCell In Range("B:B")
If IsDate(oCell) Then
If Not (Year(oCell) = Year(Now()) And Month(oCell) = Month(Now())) Then
oCell.EntireRow.Range("D1:IV1").ClearContents
oCell.ClearContents
End If
End If
Next oCell
End Sub


Jay Dean wrote
 
Back
Top