VBA Macro Help

[Anonnymouse]

Hello,

I was wondering if its possible to create a macro that deletes cells based on date.
For example I would like it to delete cell d3 and e3 since it has been longer than a year and also would like to delete column f4 and g4 and also h4 and i4.

When its deleted is there a way to make it so it doesn't shift the cells in column k,l, and M since they have formulas and it would then break the formulas.

A

B

C

D

E

F

G

H

I

J

K

L

M

1

2

Number of times

Tom

Michael

Tim

3

Tom

6/4/2023

Michael

8/1/2024

Tim

9/1/2023

1

1

1

4

Michael

4/1/2024

Michael

1/2/2023

Tom

1/1/2023

1

2

0

5

Tim

4/6/2024

Tim

4/6/2024

Michael

4/6/2024

0

1

2

6

 

[Joe4]

Try this:

Sub MyClearValues()

    Dim c As Long
    Dim r As Long
    Dim lr As Long
    
    Application.ScreenUpdating = False
    
'   Loop through columns E, G, and I
    For c = 5 To 9 Step 2
'       Find last row with data in current column
        lr = Cells(Rows.Count, c).End(xlUp).Row
'       Loop through all row starting in row 3
        For r = 3 To lr
'           See if date in current cell is more than a year old
            If Cells(r, c).Value <= DateAdd("m", -12, Date) Then
'               Clear current cell and cell to the left
                Range(Cells(r, c - 1), Cells(r, c)).ClearContents
            End If
        Next r
    Next c
    
    Application.ScreenUpdating = True
    
    MsgBox "Macro complete!", vbOKOnly
    
End Sub

 

[dreid1011]

Another option:

Private Sub ClearDates()
Dim c As Range
For Each c In Range("C3:H5")
    If c.Value < Date - 365 Then
        c.ClearContents
        c.Offset(0, -1).ClearContents
    End If
Next c
End Sub