CODEWITHSUNDEEP
vbaexcelautofilter
HPM
HPM

Reputation: 113

Excel VBA AutoFilter Codes

I want to filter a list of industries by using their industry code (e.g. C10.3, H53.1 etc.)as criteria. At the end, I want my sheet just showing those industries.

The data I want to filter is in Tabelle1 Column 21. Furthermore, I have stated my industry codes on Tabelle3 Column B. However, the following code does not execute.

Does anyone know why and how I can adjust it to my needs? 

Sub Autofilter()    
    Dim Bereich As Range
    Dim IndustryCode As Variant

    Set Bereich = Tabelle1.UsedRange
    IndustryCode = Tabelle3.Range("B:B").Value2

    Bereich.Autofilter Field:=21, Criteria1:="=" & IndustryCode  
End Sub

Upvotes: 1

Views: 220

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57743

You need to transpose the IndustryCode because this

IndustryCode = Tabelle3.Range("B:B").Value2

results in a 2 dimensional array(1 to 1048576, 1 to 1) but the criteria awaits a 1 dimensional array.

So after

IndustryCode = Application.WorksheetFunction.Transpose(IndustryCode)

you get a 1 dimensional array(1 to 65536) which you can use as Criteria1 together with Operator:=xlFilterValues.

Sub aaa()
    Dim Bereich As Range
    Dim IndustryCode As Variant

    Set Bereich = Tabelle1.UsedRange
    IndustryCode = Tabelle3.Range("B:B").Value2
    IndustryCode = Application.WorksheetFunction.Transpose(IndustryCode)

    Bereich.AutoFilter Field:=21, Criteria1:=IndustryCode, Operator:=xlFilterValues
End Sub

Note that it is not very elegant to use the whole column because the array has 65536 entries which means most are empty. A better way would be using only the range as array that is filled with data:

IndustryCode = Tabelle3.Range("B1", Tabelle3.Cells(Cells.Rows.Count, "B").End(xlUp)).Value2

This will reduce the array to the used part of column B only.

Also note that if IndustryCode (column B) contains true numbers they need to be converted into strings with a loop over the array.

ary(i) = CStr(ary(i))

as illustrated in this answer.

Upvotes: 2

Related Questions