VBA autofilter 1004 No cells were found

[Excel Trini]

Hi,

I'm using Excel office 365.

Weekly I import data into Excel then filter out certain items based on preset criteria. I do this using auto filter and a simple macro to delete the entire row.

So for example, my import might look like this:

Date​ <strike></strike>	Department​ <strike></strike>	User​ <strike></strike>	Item​ <strike></strike>	Units​ <strike></strike>
10-May​ <strike></strike>	Parts​ <strike></strike>	Jack​ <strike></strike>	Stuff I include​ <strike></strike>	2​ <strike></strike>
10-May​ <strike></strike>	Service​ <strike></strike>	Admin​ <strike></strike>	Stuff I include​ <strike></strike>	4
9-May​ <strike></strike>	Sales​	Mary​	Stuff I exclude​	2
8-May​ <strike></strike>	Parts	Jack	Stuff I include​ <strike style="color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; word-spacing: 0px;"></strike><strike></strike>	3
7-May​ <strike></strike>	Parts	Jack	Stuff I include​ <strike style="color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; word-spacing: 0px;"></strike><strike></strike>	6
9-May ​ <strike></strike>	Service	Jill	Stuff I exclude​ <strike></strike>	8
10-May​ <strike></strike>	Parts	Jack	Stuff I exclude​ <strike style="color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; word-spacing: 0px;"></strike><strike></strike>	6
7-May​ <strike></strike>	Service	Jill	Stuff I include​ <strike style="color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; word-spacing: 0px;"></strike><strike></strike>	3
9-May​ <strike></strike>	Sales	Mary	Stuff I include​ <strike style="color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; word-spacing: 0px;"></strike><strike></strike>	5
10-May​ <strike></strike>	Service	Jill	Stuff I exclude​ <strike></strike>	3
8-May​ <strike></strike>	<strike></strike> Service​ <strike></strike>	Admin	Stuff I include​ <strike style="color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; word-spacing: 0px;"></strike><strike></strike>	1
10-May​ <strike></strike>	<strike></strike> Service​ <strike></strike>	Jill	Stuff I include​ <strike style="color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; word-spacing: 0px;"></strike><strike></strike>	1

<tbody>
</tbody>

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
[/FONT]My filters identify and delete any row with department "Parts" , any row with User "Admin", and Any item with "Stuff I exclude"

So in the data in red above would all be removed.

The code I am using works fine, except for instances where the data is imported and does not have a row with the item that I want to filter out.

For example, if for one week there are no rows with the user "Admin" then I get a runtime error 1004 - No cells were found.

I am looking for a way to skip over this error (and not have to debug - because it isn't a problem for me) and continue running the rest of the macro.

There must be a way.

Let me have your thoughts.

 

[Fluff]

Can you please supply the code that you are using?

 

[Excel Trini]

Can you please supply the code that you are using?

Thanks Fluff - My Bad. Here is the code:

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Sub Data_Cleanup()
'
' Data_Cleanup Macro
'[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]    Range("a1").Select
    Selection.End(xlToRight).Select
    Selection.End(xlDown).Select
    Range(Selection, Range("A1")).Select
    With Selection
        .AutoFilter
        .AutoFilter Field:=2, Criteria1:="Parts"
        .Offset(1, 0).Select
    End With
    
    Dim numRows As Long, numColumns As Long
    numRows = Selection.Rows.Count
    numColumns = Selection.Columns.Count
    
    Selection.Resize(numRows - 1, numColumns).Select
    
    With Selection[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]    .SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End With
    
    Selection.AutoFilter
    
    Range("a1").Select
    Selection.End(xlToRight).Select
    Selection.End(xlDown).Select
    Range(Selection, Range("A1")).Select
    With Selection
        .AutoFilter
        .AutoFilter Field:=3, Criteria1:="Admin"
        .Offset(1, 0).Select
    End With
    
    Dim numRows1 As Long, numColumns1 As Long
    numRows1 = Selection.Rows.Count
    numColumns1 = Selection.Columns.Count
    
    Selection.Resize(numRows1 - 1, numColumns1).Select
    
    With Selection[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]    .SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End With
    
    Selection.AutoFilter
    
    Range("a1").Select
    Selection.End(xlToRight).Select
    Selection.End(xlDown).Select
    Range(Selection, Range("A1")).Select
    With Selection
        .AutoFilter
        .AutoFilter Field:=4, Criteria1:="Stuff I exclude"
        .Offset(1, 0).Select
    End With
    
    Dim numRows2 As Long, numColumns2 As Long
    numRows2 = Selection.Rows.Count
    numColumns2 = Selection.Columns.Count
    
    Selection.Resize(numRows2 - 1, numColumns2).Select
    
    With Selection[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]    .SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End With
    
    Selection.AutoFilter
    Range("a1").Select
    
    
End Sub
[/FONT]

 

[Fluff]

Ok, try

Sub ExcelTrini()
   Dim Ary As Variant
   Dim i As Long
   
   Ary = Array(2, "Parts", 3, "Admin", 4, "Stuff I exclude")
   For i = 0 To UBound(Ary) Step 2
      With ActiveSheet
         If .AutoFilterMode Then .AutoFilterMode = False
         .Range("A1").AutoFilter Ary(i), Ary(i + 1)
         .AutoFilter.Range.Offset(1).EntireRow.Delete
         .AutoFilterMode = False
      End With
   Next i
End Sub

 

[Excel Trini]

Ok, try

Sub ExcelTrini()
   Dim Ary As Variant
   Dim i As Long
   
   Ary = Array(2, "Parts", 3, "Admin", 4, "Stuff I exclude")
   For i = 0 To UBound(Ary) Step 2
      With ActiveSheet
         If .AutoFilterMode Then .AutoFilterMode = False
         .Range("A1").AutoFilter Ary(i), Ary(i + 1)
         .AutoFilter.Range.Offset(1).EntireRow.Delete
         .AutoFilterMode = False
      End With
   Next i
End Sub

Boom! Like Magic! Much Thanks. This has been a pain for some time now.

Great Job Fluff. Thanks buddy.

 

[Fluff]

You're welcome & thanks for the feedback