CODEWITHSUNDEEP
vbaexcel
Daruki
Daruki

Reputation: 491

Autofilter with 3 columns

I currently have the code to filter for specific items in 3 columns. Is there a limitation to autofilter that only allows 2 columns to be filtered? once I add in the 3rd column filter, i get the "Autofilter method of range class failed" error. if there is a limitation, are there any workarounds?

    Public Sub autofilterOffical()
Dim ws As Worksheet
Dim wsOut As Worksheet
Dim a As String
Dim b As String
Dim c As String
Dim d As String
Dim e As String
Dim f As String
Dim g As String
Dim h As String
Dim i As String
Dim j As String
Dim k As String
Dim l As String
Dim m As String



Set ws = Worksheets("Data")
AutoFilterMode = False
a = ws.Range("bq6")
b = ws.Range("bq7")
c = ws.Range("bq8")
d = ws.Range("bq9")
e = ws.Range("bq10")
f = ws.Range("bq11")
g = ws.Range("bq12")
h = ws.Range("bq13")
i = ws.Range("bq14")
j = ws.Range("bq15")
k = ws.Range("bq16")
l = ws.Range("bq17")
m = ws.Range("bq18")

If ws.Range("br18") = "1" Then
ws.Range("A:Bn").autofilter field:=66, Criteria1:=Array(m), _
Operator:=xlFilterValues
Else
End If


If ws.Range("br6") = "1" Then
ws.Range("a:bn").autofilter field:=64, Criteria1:=Array(a), _
Operator:=xlFilterValues
ElseIf ws.Range("br6") = "2" Then
ws.Range("a:bn").autofilter field:=64, Criteria1:=Array(a, b), _
Operator:=xlFilterValues
ElseIf ws.Range("br6") = "3" Then
ws.Range("a:bn").autofilter field:=64, Criteria1:=Array(a, b, c), _
Operator:=xlFilterValues
ElseIf ws.Range("br6") = "4" Then
ws.Range("a:bn").autofilter field:=64, Criteria1:=Array(a, b, c, d), _
Operator:=xlFilterValues
ElseIf ws.Range("br6") = "5" Then
ws.Range("a:bn").autofilter field:=64, Criteria1:=Array(a, b, c, d, e), _
Operator:=xlFilterValues
ElseIf ws.Range("br6") = "6" Then
ws.Range("a:bn").autofilter field:=64, Criteria1:=Array(a, b, c, d, e, f), _
Operator:=xlFilterValues
ElseIf ws.Range("br6") = "7" Then
ws.Range("a:bn").autofilter field:=64, Criteria1:=Array(a, b, c, d, e, f, g), _
Operator:=xlFilterValues
ElseIf ws.Range("br6") = "8" Then
ws.Range("a:bn").autofilter field:=64, Criteria1:=Array(a, b, c, d, e, f, g, h), _
Operator:=xlFilterValues
ElseIf ws.Range("br6") = "9" Then
ws.Range("a:bn").autofilter field:=64, Criteria1:=Array(a, b, c, d, e, f, g, h, i), _
Operator:=xlFilterValues
ElseIf ws.Range("br6") = "0" Then

Else
End If

If ws.Range("br15") = "1" Then
ws.Range("a:bn").autofilter field:=63, Criteria1:=Array(j), _
Operator:=xlFilterValues
ElseIf ws.Range("br15") = "2" Then
ws.Range("a:bn").autofilter field:=63, Criteria1:=Array(j, k), _
Operator:=xlFilterValues
ElseIf ws.Range("br15") = "3" Then
ws.Range("a:bn").autofilter field:=63, Criteria1:=Array(j, k, l), _
Operator:=xlFilterValues
Else
End If




Call copy


End Sub

Upvotes: 0

Views: 166

Answers (1)

YowE3K
YowE3K

Reputation: 23994

Your third filter is sometimes (often? usually?) failing because you are trying to filter on the 66th column of the range.

However, if BR6 is between 1 and 9 then you have defined the filter range to be A:BL which is only 64 columns wide.

And if BR6 is not between 1 and 9, but BR15 is between 1 and 3, then you have defined the filter range to be A:BK which is only 63 columns wide.

The only way that the final filter will work is if you aren't doing either of the first two filters, because in that situation you are setting the range to be A:BQ.

I would suggest that you set your filter to be on columns A:BN in all situations.

Upvotes: 2

Related Questions