VBA CODE HELP

[zmbe_clare]

Need some help with a code that lets me clear contents for a cell that is over a year old, then also clears the contents on the remaining row until a certain point. The first cell in the row will have the date, and the next two cells will have data that needs to be cleared, adn the rest of the contents in the sheet with have to move up so there is not a gap at the top of the sheet.

 

[davesexcel]

Show the code you need help with.

 

[zmbe_clare]

This is the code that i have so far, and all it does is deletes the cell with the date older then a year.

Sub Date_test()
Dim ddate As Date
Dim rCell As Range
Dim r As Long

With Sheets("DateTest")
For Each rCell In .Range(.Cells(18, "B"), .Cells(31, "B").End(xlUp))
If rCell <= Now() - 365 Then
rCell.ClearContents
End If
Next rCell


End With

End Sub

 

[davesexcel]

.range(.cells(rcell.row,1), .cells(rcell.row,3).clearcontents.

I am not sure how you would end up with a gap at the top?

You could delete the cells and shift cell up if required or just delete the entire row unless there is other stuff next to it you don't want deleted.

 

[zmbe_clare]

.range(.cells(rcell.row,1), .cells(rcell.row,3).clearcontents.

I am not sure how you would end up with a gap at the top?

You could delete the cells and shift cell up if required or just delete the entire row unless there is other stuff next to it you don't want deleted.

That is actually what I was looking to accomplish, and I'm new to VBA coding; and I'm not sure how to code that action.

 

[davesexcel]

When in doubt, use your macro recorder to see what the code looks like.