VBA Delete Table Rows Given Date & Contents of Cell

[Damien Hartzell]

What could I add to this to have it do the row delete for anything prior to 4/1 of the current year (other than if a certain cell contains the text "FMLA")?

Function ActiveTable(rng As Range) As ListObject

    Dim rv As ListObject
    If rng Is Nothing Then Exit Function
    For Each rv In rng.Parent.ListObjects
        If Not Intersect(rng, rv.Range) Is Nothing Then
            Set ActiveTable = rv
            Exit Function
        End If
    Next rv

End Function

Sub April1Reset()

    If MsgBox("Are you sure you want to remove dates (other than FMLA) prior to April 1 of this year?", vbYesNo + vbQuestion) = vbNo Then End
    
    Tenure = Range("H5").Text
    Un = Range("H7").Text
    weekend = Range("H3").Text
    
    
        ActiveSheet.Cell("H16").Activate

    
    Dim tbl As ListObject, tblnm As String, rng As Range
    
    Set tbl = ActiveTable(ActiveCell)
        
    If tbl Is Nothing Then Exit Sub
    
    tblnm = tbl.Name
    
    Set rng = ActiveSheet.Range(tblnm & "[[#Headers],[Name]]")
        
    'Delete all table rows prior to 4/1 of current year
        With tbl.DataBodyRange
            If .Cells(2, H).Value < .Formula = "=DATE(Year(TODAY()), 4, 1)" Then
                .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Rows.Delete
            End If
        End With
        
End Sub

 

[Damien Hartzell]

All, call off the hounds! I figured it out!

Sub April1Reseta()

    If MsgBox("Are you sure you want to remove dates (other than FMLA) prior to April 1 of this year?", vbYesNo + vbQuestion) = vbNo Then End

    Range("H16").Select

    Dim tbl As ListObject, tblnm As String, rng As Range

    Set tbl = ActiveSheet.ListObjects(1)
    tblnm = tbl.Name
    
    If tbl Is Nothing Then Exit Sub
    
        'Set April 1 of current year parameters
    Dim Apr1
    Dim ThisYr
    
    ThisYr = Year(Date)
    Apr1 = DateValue("April 1")
    
        'Delete all table rows prior to April 1 of current year except FMLA
    For i = tbl.ListRows.Count To 1 Step -1
        If tbl.DataBodyRange(i, 6).Value <> "FMLA" Then
        If tbl.DataBodyRange(i, 6).Value <> "Unsch Paid FMLA" Then
                If tbl.DataBodyRange(i, 1).Value < Apr1 Then
                    tbl.ListRows(i).Delete
                End If
        End If
        End If
    Next i
    
End Sub