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
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.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments