Autofilter in VBA

BDexcel

New Member
Joined
Jun 28, 2017
Messages
44
Could someone help me please?

Initially I had coded a filter to remove 1 criteria as below:

Filter = WorksheetFunction.Match("Class Name", Rows("9:9"), 0)
ActiveSheet.Range("A9:R6000").AutoFilter field:=Filter, Criteria1:="<>Deposits"

However I need it to exclude multiple items now... 13 Criteria to be exact.

So I tried the below code:
Filter = WorksheetFunction.Match("Class Name", Rows("9:9"), 0)
ActiveSheet.Range("A9:R6000").AutoFilter field:=Filter, Criteria1:="<>Deposits", _
Operator:=xlAnd, Criteria2:="<>Cash", _
Operator:=xlAnd, Criteria3:="<>Commodities", _
Operator:=xlAnd, Criteria4:="<>Metals", _
Operator:=xlAnd, Criteria5:="<>Stock", _
Operator:=xlAnd, Criteria6:="<>Bonds", _
Operator:=xlAnd, Criteria7:="<>Corporate", _
Operator:=xlAnd, Criteria8:="<>Placements", _
Operator:=xlAnd, Criteria9:="<>Securities", _
Operator:=xlAnd, Criteria10:="<>HBonds", _
Operator:=xlAnd, Criteria11:="<>Rights", _
Operator:=xlAnd, Criteria12:="<>Investments", _
Operator:=xlAnd, Criteria13:="<>Bills"

But I an now getting a Run-time error '1004'.

Can someone help me please? I've searched for Arrays etc but cant get a code to exclude all these criteria.
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Re: Help with an autofilter in VBA please?

Plase try this code.
Code:
Dim x
x = Application.Transpose(Range(cells(10, Filter), cells(Rows.Count, Filter).End(xlUp)).Value)
x = filter(x, "Cash", False)
x = filter(x, "Commodities", False)
x = filter(x, "Metals", False)
x = filter(x, "Stock", False)
.
.
.
.
ActiveSheet.Range("A9").AutoFilter field:=filter, Criteria1:=x, Operator:=xlFilterValues
 
Upvote 0
Re: Help with an autofilter in VBA please?

Thanks Takae, I tried this but i'm getting a Compile Error: Argument not optional at the "filter" below

= Application.Transpose(Range(cells(10, Filter), cells(Rows.Count, Filter).End(xlUp)).Value)
 
Upvote 0
Re: Help with an autofilter in VBA please?

The "Filter" is a variable which you have set. It will not be an error.
Don't delete this line.
Filter = WorksheetFunction.Match("Class Name", Rows("9:9"), 0)
 
Upvote 0
Re: Help with an autofilter in VBA please?

I tired this but it wont work, have I done something wrong?
Sub sfilter()
With ActiveSheet

Dim Filter As Long

Filter = WorksheetFunction.Match("Class Name", Rows("9:9"), 0)

Dim x
x = Application.Transpose(Range(Cells(10, Filter), Cells(Rows.Count, Filter).End(xlUp)).Value)
x = Filter(x, "Cash", False)
x = Filter(x, "Commodities", False)
x = Filter(x, "Metals", False)
x = Filter(x, "Stock", False)
ActiveSheet.Range("A9").AutoFilter field:=Filter, Criteria1:=x, Operator:=xlFilterValues

End With
End Sub
 
Upvote 0
Re: Help with an autofilter in VBA please?

The "Filter" of variable is confusing. I changed it.

Code:
Sub sfilter()Dim col As Long
Dim x
With ActiveSheet
    col = WorksheetFunction.Match("Class Name", Rows("9:9"), 0)
    x = Application.Transpose(.Range(.Cells(10, col), .Cells(Rows.Count, col).End(xlUp)).Value)

    x = Filter(x, "Cash", False)
    x = Filter(x, "Commodities", False)
    x = Filter(x, "Metals", False)
    x = Filter(x, "Stock", False)
   .Range("A9").AutoFilter field:=col, Criteria1:=x, Operator:=xlFilterValues
End With
End Sub
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

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