I am trying to have AutoFilter applied to all data and then filter out all cells except 'Blanks" in column C. It selects all data on the worksheet for the first line but then gives me a run-time error - 'Named argument not found'.
Selection.CurrentRegion.Select
ActiveSheet.AutoFilter Field:=3, Criteria1:="blanks"
I would like the sheet to AutoFilter and filter column C to only show Blanks.
Implicit late binding strikes again! ActiveSheet is an Object, not a Worksheet - any member calls you make directly against it, are inherently late-bound. That is, they can only be resolved at run-time, and you get zero compiler or IDE assistance when writing the code.
If you declared a Worksheet object:
Dim ws As Worksheet
...and assigned it to the ActiveSheet:
Set ws = ActiveSheet
...then tried to invoke its AutoFilter property:
ws.AutoFilter
...you'd quickly notice something is off: Worksheet.AutoFilter isn't a method, but a property (and it's Nothing if filtering is off). You mean to use the Range.AutoFilter method.
Working with Selection will work, but will be just as late-bound as working with ActiveSheet.
Work with a Range instead - CurrentRegion just happens to be exactly that!
ws.Range("A1").CurrentRegion.AutoFilter
And now when you type the Space after that member call, you'll get parameter quick-info showing you what the parameters are - note that Field and Criteroa1 are respectively the first and second parameters in the member's signature - this means the named arguments are actually redundant / the parameter names and the := operators can be omitted, and the values passed normally:
ws.Range("A1").CurrentRegion.AutoFilter 3, "blanks"
ws.Range("A1").CurrentRegion.AutoFilter Field:=3, Criteria1:="blanks" '<~ exactly equivalent to the above
