Autofilter in VBA

[BDexcel]

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.

 

[Takae]

Re: Help with an autofilter in VBA please?

Plase try this 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

 

[BDexcel]

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)

 

[Takae]

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)

 

[BDexcel]

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

 

[Takae]

Re: Help with an autofilter in VBA please?

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

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

 

[BDexcel]

Re: Help with an autofilter in VBA please?

Thanks again Takae