VBA Delete Table Rows Given Criteria

[Damien Hartzell]

This code is simply trying to remove entries from a table prior to 12 months from today's date. I keep getting a 1004 error that points to the following: "Set rng = ActiveSheet.Range(tblnm & "[[#Headers],[Name]]")". The table headers beging are the Range H15:M15. The rows vary, so the range is dynamic. I'm pretty new to VBA, so I'm confused as to what the error is trying to tell me. Anyone able to provide a fix?

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 Reset12Month()

    If MsgBox("Are you sure you want to remove absences prior to 12 months before today's date? (If Non-Union, this will only include FMLA. If Union, this will include all absence entries.)", vbYesNo + vbQuestion) = vbNo Then End

    Range("H15").Select

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

    Set tbl = ActiveTable(ActiveCell)
    tblnm = tbl.Name
    
    If tbl Is Nothing Then Exit Sub
    
    Set rng = ActiveSheet.Range(tblnm & "[[#Headers],[Name]]")

    'ActiveTable = ActiveCell.ListObject.Name
                    'With Range(ActiveTable).ListObject
                    With rng
                    ''
                        Range("H15:M15").Select
                        Range(Selection, Selection.End(xlDown)).Select
                    End With
                    
        'Delete all table rows prior to 12 months of current date
        With tbl.DataBodyRange
            If .Cells(2, H).Value < .Formula = "=EDATE(TODAY(), -12)" Then
                .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Rows.Delete
            End If
        End With
    
End Sub

 

[igold]

You're welcome, I was happy to help. Thanks for the feedback!