How to have worksheet_change(byval target by range) to run with workbook_open

[Miler13]

I want to be able to have a workbook do a function when opened. So I want to have a targeted column in a sheet, if a date is 90 days prior to the current date, it will automatically delete the section associated with that date. Example - if today is 4/23/19 and a previous input in a sheet is 1/23/19, it will automatically delete cells associated with that 1/23/19 date upon the workbook opening. I hope this makes sense.

 

[bobsan42]

use this in ThisWorkbook code module:

Option Explicit


Private Sub Workbook_Open()
    Const daysDiff = [COLOR=#ff0000]90 [/COLOR]'number of days to go back[COLOR=#ff0000][/COLOR]
    Const sheetNm = "[COLOR=#ff0000]Sheet1[/COLOR]" 'name of the worksheet containing the data to be deleted
    Const dateCol = "[COLOR=#ff0000]A:A[/COLOR]" 'column with dates to check
    Dim rngColl As Range, wsh As Worksheet, rng As Range, cc As Range
    
    Set wsh = ThisWorkbook.Worksheets(sheetNm)
    With wsh.Range(dateCol)
        Set rng = Union(.SpecialCells(xlCellTypeFormulas), .SpecialCells(xlCellTypeConstants))
    End With
    For Each cc In rng
        If rng.Value = (Date - daysDiff) Then [COLOR=#0000ff]'maybe here you should use [B]<=[/B] instead of =[/COLOR]
            If Not rngColl Is Nothing Then
                Set rngColl = Union(rngColl, cc)
            Else
                Set rngColl = cc
            End If
        End If
    Next cc
    If Not rngColl Is Nothing Then rngColl.EntireRow.Delete
    
    Set rngColl = Nothing
    Set wsh = Nothing
    Set cc = Nothing
    Set rng = Nothing
End Sub

Adjust the values in RED to match your case.

 

[Miler13]

I tried this but getting an error stating no rng was found.

 

[bobsan42]

did you update the values in RED to match your workbook?