Delete rows using AutoFilter and SpecialCells properties

Hello forum,

The line that is meant to apply the autofilter to the range A5 to Q & LastRow is not executing as expected.

The error message I get is "can't be applied to the selected range. Select a single cell in a range and try again."

How can I correct this?

Sub AFDeleteRows()
    
Dim LastRow As Long
    LastRow = Cells(Rows.Count, 10).End(xlUp).Row
    
Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1")

    On Error Resume Next
    ws.ShowAllData
    On Error GoTo 0
    
'The line below is returning the error
    ws.Range("A5:Q" & LastRow).AutoFilter Field:=9, Criteria1:=""
    Application.DisplayAlerts = False
    ws.Range("I5:I" & LastRow).SpecialCells(xlCellTypeVisible).Delete
    Application.DisplayAlerts = True
    
    On Error Resume Next
    ws.ShowAllData
    On Error GoTo 0
    
End Sub

Happy New Year!

Hi Rory, I tried your suggestion and received the same error message. The macro is being used on an excel file that is system generated and opened from email (Outlook). As far as I know there are no ranges set before I get the file and there are no other filters in use, that I can see. I'm still a novice with using VBA so I apologize if I am missing any of the basics and I probably don't have a thorough understanding of Range and Range Properties. To my thinking it's strange that the error message is requesting that a single cell needs to be selected in a range and to try again. Isn't the whole idea of using a filter based on multiple cells?

Sub AFdeleteMacro()
    
Dim LastRow As Long
    LastRow = Cells(Rows.Count, 10).End(xlUp).Row
    
Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1")

    On Error Resume Next
    ws.AutoFilterMode = False
    On Error GoTo 0
    
    ws.Range("A5:Q" & LastRow).AutoFilter Field:=9, Criteria1:=""
    Application.DisplayAlerts = False
    ws.Range("I5:I" & LastRow).SpecialCells(xlCellTypeVisible).Delete
    Application.DisplayAlerts = True
    
    On Error Resume Next
    ws.AutoFilterMode = False
    On Error GoTo 0
    
End Sub

Excellent that worked! But before I read your post I was playing with this and tried to set the Worksheet differently using the code below.

The error I encountered was "object required". Isn't a Worksheet an object?

Dim wsActiveSheet As Worksheet
Set wsActiveSheet = Application.ActiveSheet

Thanks and Happy New Year!