When the Date value in cell B2 is changed, I would like the Delete_Table_Rows macro (see below) to run prior to the rest of the code contained in the Worksheet_Change code (also below). This macro should only run if there is data in the 38th row and beyond (39, 40, 41, etc.). This is necessary in order to avoid having the macro throw an error. Please note that I am referring to table row and not worksheet row. I tried adding a Call Delete_Table_Rows line somewhere in the Change code but haven’t been successful. It seems perhaps something else must be written with it to allow this to be compatible. Please help!
My goal is to have this table be able to reset itself and accept new data. I somehow will need to find a way to clear out the C2:C38 cells as well but, at the moment, am satisfied with doing so manually.
Public Sub Delete_Rows_and_Clear_Cells_In_Table()
Dim table As ListObject
Set table = Sheets("Phase").ListObjects("Phaseline")
With table
'Delete 38th data row to last data row
.DataBodyRange.Rows("38:" & .ListRows.Count).Delete
End With
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim intersection As Range
Set intersection = Intersect(Target, Range("B2"))
If Not intersection Is Nothing Then
Dim lLastrow As Long, oldDate As Date, newDate As Date
With Sheets("Phase")
lLastrow = .Range("B" & .Rows.Count).End(xlUp).Row
oldDate = .Range("B37").Value
newDate = Date
Dim lEndrow As Long
lEndrow = lLastrow + 367
Do While oldDate < newDate And lLastrow <= lEndrow
oldDate = oldDate + 1
lLastrow = lLastrow + 1
.Range("B" & lLastrow).Value = oldDate
Loop
End With
End If
End Sub
Bookmarks