VBA autofilter 1004 No cells were found

E

Excel Trini

New Member
Joined
Jun 16, 2010
Messages
9
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:

[TABLE="width: 500"]
<tbody>[TR]
[TD]
Date​
<strike></strike>
[/TD]
[TD]
Department​
<strike></strike>
[/TD]
[TD]
User​
<strike></strike>
[/TD]
[TD]
Item​
<strike></strike>
[/TD]
[TD]
Units​
<strike></strike>
[/TD]
[/TR]
[TR]
[TD]
10-May​
<strike></strike>
[/TD]
[TD]
Parts​
<strike></strike>
[/TD]
[TD]
Jack​
<strike></strike>
[/TD]
[TD]
Stuff I include​
<strike></strike>
[/TD]
[TD]
2​
<strike></strike>
[/TD]
[/TR]
[TR]
[TD]
10-May​
<strike></strike>
[/TD]
[TD]
Service​
<strike></strike>
[/TD]
[TD]
Admin​
<strike></strike>
[/TD]
[TD]
Stuff I include​
<strike></strike>
[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]
9-May​
<strike></strike>
[/TD]
[TD]
Sales​
[/TD]
[TD]
Mary​
[/TD]
[TD]
Stuff I exclude​
[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]
8-May​
<strike></strike>
[/TD]
[TD]Parts
[/TD]
[TD]Jack[/TD]
[TD]
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>
[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]
7-May​
<strike></strike>
[/TD]
[TD]Parts[/TD]
[TD]Jack[/TD]
[TD]
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>
[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]
9-May ​
<strike></strike>
[/TD]
[TD]Service[/TD]
[TD]Jill[/TD]
[TD]
Stuff I exclude​
<strike></strike>
[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]
10-May​
<strike></strike>
[/TD]
[TD]Parts[/TD]
[TD]Jack[/TD]
[TD]
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>
[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]
7-May​
<strike></strike>
[/TD]
[TD]Service[/TD]
[TD]Jill[/TD]
[TD]
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>
[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]
9-May​
<strike></strike>
[/TD]
[TD]Sales[/TD]
[TD]Mary[/TD]
[TD]
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>
[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]
10-May​
<strike></strike>
[/TD]
[TD]Service[/TD]
[TD]Jill[/TD]
[TD]
Stuff I exclude​
<strike></strike>
[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]
8-May​
<strike></strike>
[/TD]
[TD]<strike></strike>
Service​
<strike></strike>
[/TD]
[TD]Admin[/TD]
[TD]
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>
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]
10-May​
<strike></strike>
[/TD]
[TD]<strike></strike>
Service​
<strike></strike>
[/TD]
[TD]Jill[/TD]
[TD]
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>
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[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.
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Sort by date Sort by votes
Can you please supply the code that you are using?
 
Upvote 0
Fluff said:
Can you please supply the code that you are using?
Click to expand...

Thanks Fluff - My Bad. Here is the code:
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]
 
Upvote 0
Ok, try
Code: 
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
 
Upvote 0
Fluff said:
Ok, try
Code: 
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
Click to expand...


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

Great Job Fluff. Thanks buddy.
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C
S
  • Question Question
Lookup If Blank Or only have certain value
2
Replies
14
Views
2K
sandy666
sandy666
A
  • Question Question
Formula for conditional formating - NOT AS EASY AS IT SEEMS
Replies
3
Views
914
Eric W
Eric W
J
  • Question Question
COUNTIFS Hours and minutes in range
Replies
3
Views
902
CyrusTheVirus
CyrusTheVirus
R
  • Question Question
MAXIF and return another column
2 3
Replies
21
Views
3K
Aryatect
Aryatect

Forum statistics

Threads
1,223,894
Messages
6,175,254
Members
452,624
Latest member
gregg777

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top