macro stops on 1004 error when no rows correspond to autofilter criteria

[raduzsz]

Hello,

In an excel worksheet, I am trying to filter out and delete all rows that contains the string "Services" in the column AH. The below code works fine, however it stops if there are no "Services" in the column AH (Runtime error code 1004, application-defined or object defined error). I assume there should be some kind of "On error resume next" code added, but I have no clue how to do that. I have no debug option in my code and no line is highlighted after the error message appears. Could you please advise? Thanks.

With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set rng = .Range("AH1:AH" & LastRow)
rng.AutoFilter Field:=1, Criteria1:="Services", _
Operator:=xlFilterValues
ActiveSheet.Rows(1).Hidden = True
Set rng = rng.Columns("A").SpecialCells(xlCellTypeVisible)
If Not rng Is Nothing Then rng.EntireRow.Delete
End With
Range("AH1:AH" & LastRow).RowHeight = 12.75
ActiveSheet.Rows(1).Hidden = False
AutoFilterMode = False

 

[Marc L]

Hi !

No need SpecialCells but use SUBTOTAL worksheet function
to check if there are some filtered lines :
Evaluate("SUBTOTAL(103,AH1:AH" & LastRow &")")

Or better before any filter via COUNTIF worksheet function …
​