EntireRow.Delete not working

[morsagmon]

Hi.

I need to delete rows from a ListObject very fast, based on criteria applied on a date column.

I get the 1004 Delete method of Range class failed error on the EntireRow.Delete line:

Sub DeleteOldPhoneCalls(ByRef tblCalls As ListObject)
    
    Dim rngDelete As Range

    tblCalls.Range.AutoFilter Field:=1, Criteria1:="<" & Now() - Range("Days_to_Keep_Phone_Calls").Value
    Set rngDelete = tblCalls.Range.SpecialCells(xlCellTypeVisible)
    tblCalls.Range.AutoFilter

    If Not rngDelete Is Nothing Then rngDelete.EntireRow.Delete

    Set rngDelete = Nothing
End Sub

First column of this table is of type Date and has valid dates in it.

Here's how I call this sub:

Dim tblCalls As ListObject
Set tblCalls = Sheets("Calls Table").ListObjects("PhoneCallsTable")
Call DeleteOldPhoneCalls(tblCalls)

Any ideas?
Thanks!

 

[James006]

Hi,

My first assumption would be to replace ...Now() by Date ....

HTH

 

[morsagmon]

Thanks, James,
Didn't make any difference...

 

[morsagmon]

This is what works eventually:

Sub DeleteOldPhoneCalls(ByRef tblCalls As ListObject)

    Dim rngDelete As Range
    
    Range(tblCalls).Select
    tblCalls.Range.AutoFilter Field:=1, Criteria1:="<" & Date - Range("Days_to_Keep_Phone_Calls").Value
    On Error GoTo CloseSub
    Set rngDelete = Selection.SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
    Selection.AutoFilter
    If Not rngDelete Is Nothing Then rngDelete.Delete
    

CloseSub:
    tblCalls.Range.AutoFilter Field:=1
    Set rngDelete = Nothing
End Sub

Thanks!
Mor

 

[James006]

Glad you have managed to solve your problem ...

Thanks for your thanks ...