VBA Autofilter when result is nothing ERROR in the code

[reddsable]

I have the below code and I get an error when trying to run it I get the error

Run-time error '1004':Autofilter method of Range class failed

This is because after filtering sometimes I have no results. ​

I would like to have a code that tells to ignore the code if the Autofilter returns no result. I have tryied also including the code

On Error Resume Next

On Error GoTo 0​

​

Any idea how to do that? Thanks!​

_{^{<strike>
</strike>}}​

<strike></strike>​[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Worksheets("DATA").AutoFilterMode = False
If Not ActiveSheet.AutoFilterMode Then
ActiveSheet.Range("A1").AutoFilter
End If

Selection.AutoFilter Field:=95, Criteria1:=Array("ABC", "DEF", "GHI", "JKL"), Operator:=xlFilterValues

ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible).Rows.Count

If ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible).Rows.Count > 1 Then

ActiveSheet.Range("2:" & ActiveSheet.UsedRange.Rows.Count).SpecialCells(xlCellTypeVisible).Select
Selection.Delete Shift:=xlUp
Selection.AutoFilter

End If<strike></strike>
[/FONT]​

 

[Joe4]

Welcome to the Board!

Put the "On Error Resume Next" line before the filter code, and the "On Error GoTo 0" line after the filter code.

 

[reddsable]

If I do that, it's deleting my data that shouldn't be deleted, because the filter did not work, considering there were no values "ABC", "DEF" etc so the filter did not filter any data, but all my data was deleted

 

[Fluff]

Maybe something like

   With ActiveSheet
      .Range("A1").AutoFilter 95, Array("ABC", "DEF"), xlFilterValues
      .AutoFilter.Range.Offset(1).EntireRow.Delete
      .AutoFilterMode = False
   End With

 

[Joe4]

So, is it that you only want to delete the rows containing "ABC", "DEF", "GHI", or "JKL"?

 

[reddsable]

So, is it that you only want to delete the rows containing "ABC", "DEF", "GHI", or "JKL"?

Yes I want to delete only the rows containing

"​

ABC", "DEF", "GHI", or "JKL", but sometimes it happens in my reports that I do not have these values. So when I don't have the values the code is deleting all my existing data​

 

[Joe4]

Take a look at the code that Fluff posted and see if that does it for you (though I think you will need to add the other values in your array to his code, I am guessing he just did a simple test on the two).

 

[reddsable]

Finally I added my other values and it seems to be working. Thanks everybody!

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Worksheets("DATA").AutoFilterMode = False
If Not ActiveSheet.AutoFilterMode Then
ActiveSheet.Range("A1").AutoFilter
End If[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Selection.AutoFilter Field:=14, Criteria1:=Array("ABC", "DEF", "GHI", "JKL"), Operator:=xlFilterValues[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]If ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible).Rows.Count > 1 Then[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]ActiveSheet.Range("2:" & ActiveSheet.UsedRange.Rows.Count).SpecialCells(xlCellTypeVisible).Select
Selection.Delete Shift:=xlUp
Selection.AutoFilter[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Else: Selection.AutoFilter Field:=14, Criteria1:=Array _
("MNO", "PQR", "STU"), Operator:=xlFilterValues[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
End If[/FONT]

 

[Fluff]

A word of warning, that will only work correctly if if row 2 contains one of the filtered values.