Delete Visible Rows Filtered Using .SpecialCells Not Working

[Firesword]

Hi All (and Happy New Year)

I have some data on a worksheet that has a date the data was added. To stop the report getting to large I only want to keep the current and last year's data and delete the rest. The code listed below needs to filter the data and delete the visible rows. I'm getting a run error but I cannot work out what's wrong.

Currently all the checks are working and the correct date range is being displayed, but I'm getting a run time error when it trys to delete the rows. I've added some comments to the data in case that helps. Any help would be appreciated.

Dim lStartDate, lLastRow, lVisibleRow, lEndDate  As Long

    With Sheet12
        If .ListObjects(1).showAutofilter Then .ListObjects(1).AutoFilter.ShowAllData 'checks if there is a filter in place and if so clears the filter
        lStartDate = CDate("01/" & Month(Now()) & "/" & Year(Now()) - 1) 'Gets the first day and month of last year
'lStartDate =44197
        .Range("A1").AutoFilter Field:=16, Criteria1:="<" & lStartDate 'Hide current and last year's data, shows the rest
        lLastRow = .Cells(Rows.Count, 1).End(xlUp).Row 'Gets the total rows of the sheet
'lLastRow = 470399
        lVisibleRow = Application.WorksheetFunction.Subtotal(3, .Range("P:P")) 'Gets the visible rows
'lVisibleRow = 22158

        If (lLastRow > 1) And (lVisibleRow > 1) Then .Range("A2:A" & lLastRow).SpecialCells(xlCellTypeVisible).EntireRow.Delete
'checks to make sure there is data to delete which is working. If there is any data then delete it this is the bit that's not working.
'Not working = .Range("A2:A" & lLastRow).SpecialCells(xlCellTypeVisible).EntireRow.Delete
'Error msg = Run-Time error '1004':- Application-defined or object-defined error

        If .ListObjects(1).showAutofilter Then .ListObjects(1).AutoFilter.ShowAllData 'checks if there is a filter in place and if so clears the filter
    End With

Thanks

Simon

 

[BSALV]

start small with 12, not immidately +22.000
you delete the first row with this method (no importance, visible or not)

     With sheet12.ListObjects(1)
          MyNumberOfRows = 12                                   'number of listrows you calculated with your autofilter or otherwise
          .DataBodyRange.Resize(MyNumberOfRows).Delete
     End With

 

[Firesword]

start small with 12, not immidately +22.000
you delete the first row with this method (no importance, visible or not)

     With sheet12.ListObjects(1)
          MyNumberOfRows = 12                                   'number of listrows you calculated with your autofilter or otherwise
          .DataBodyRange.Resize(MyNumberOfRows).Delete
     End With

Thank you for the help and posting so quickly. It's working as it should now