Error AutoFilter Method of Range Class Failed

barne2100

New Member
Joined
Mar 6, 2019
Messages
2
Hello! I am relatively new to Macros so bare with me. Here is the code that I am working with:

Sub Step4()
'
' Step4 Macro
'
' Keyboard Shortcut: Ctrl+Shift+N
'
ActiveSheet.AutoFilterMode = False
Range("K1").AutoFilter Field:=11, Criteria1:=Array( _
"#N/A"), Operator:=xlFilterValues, Criteria2:=Array(0, "1/0/1900")
Range("K3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.EntireRow.Delete
ActiveSheet.Range("$A$2:$P$96").AutoFilter Field:=11
End Sub


The error occurs in the area that is red. I am unable to figure out what the issue is. My goal is to filter to anything that has an N/A or a 1900 Date (basically 0), and delete those rows. Any help would be great! Thank you
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
I think your data table is in columns A:P, with headers in row 2

100000 is big enough to ALWAYS include all your data
Code:
ActiveSheet.Range("A2:P100000").AutoFilter Field:=11, Criteria1:=Array("#N/A"), Operator:=xlFilterValues, Criteria2:=Array(0, "1/0/1900")

If headers are in row 1, you can always use use the simple
Code:
ActiveSheet.Range("A:P").AutoFilter Field:=11, Criteria1:=Array("#N/A"), Operator:=xlFilterValues, Criteria2:=Array(0, "1/0/1900")
 
Upvote 0
I think your data table is in columns A:P, with headers in row 2

100000 is big enough to ALWAYS include all your data
Code:
ActiveSheet.Range("A2:P100000").AutoFilter Field:=11, Criteria1:=Array("#N/A"), Operator:=xlFilterValues, Criteria2:=Array(0, "1/0/1900")


Thank you for your reply! You are correct, my headers are in row 2. The code you listed did not work though, the same error occurred. Any other ideas?
 
Upvote 0

Forum statistics

Threads
1,215,432
Messages
6,124,858
Members
449,194
Latest member
HellScout

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