Adding Clear Contents command to a current VBA code

[hn61122]

New to VBA I have a code that will updated the Year automatically 6 months after expiration. But now I would like it to clear the contents of the cell once it updates to the new year. Below is my current code:

Private Sub Worksheet_Activate()
Dim N As Long, r As Range, rng As Range
Dim d As Date
N = Cells(Rows.Count, "B").End(xlUp).Row
Set rng = Range("B7:B" & N)

For Each r In rng
If IsDate(r.Value) Then
d = r.Value
If d < Date - 180 Then
r.Value = DateSerial(Year(d) + 1, Month(d), Day(d))
End If
End If
Next r
End Sub

When each cell updates to its New Year i would like for the their specified range of cells to clear contents at the same time:
Example:
When b7 Updates contents in cell B8-AO10 to clear
When b11 Updates contents in cell B12-AO14 to clear


** I have not wrote anything to clear contents because everything write doesn't work, more than likely because I don't know much about VBA's. I have researched for days and cant figure this out. Any help would be greatly appreciated.

 

[Ezguy4u]

hn just a suggestion. Have you tried using the Record feature? That might give you a hint. Also, can you use that XL2BB software? You will get lots of solutions if you use it. Also when you say it doesn't work, what doesn't work?

 

[kevin9999]

Try the following with a copy of your file. Put this line of code:

r.Offset(1).Resize(3, 40).ClearContents

Immediately after this line:

r.Value = DateSerial(Year(d) + 1, Month(d), Day(d))

 

[hn61122]

Try the following with a copy of your file. Put this line of code:

r.Offset(1).Resize(3, 40).ClearContents

Immediately after this line:

r.Value = DateSerial(Year(d) + 1, Month(d), Day(d))

Omg, this worked so well and was exactly what needed. I really appreciate your help!!!!!! i think i can finally close this workbook to night! OMG,, thank you, thank you, thank you!

 

[kevin9999]

You're welcome & thanks for the feedback.