Excel VBA AutoFilter Codes

Question

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

what is `NACECode` ? And "*does not execute*" does not describe what happens. — Pᴇʜ, Mar 07 '18 at 14:20
did you try to use `, Operator:=xlFilterValues`? Like `Bereich.Autofilter Field:=21, Criteria1:="=" & IndustryCode, Operator:=xlFilterValues` — Pᴇʜ, Mar 07 '18 at 14:25
@PEH: "does not execute" means, when I try to run the macro it does not work and shows "Run-Time error '13': Typen unverträglich — HPM, Mar 07 '18 at 14:26
Error message without telling in which line it occurs is usless in most cases. So in which line do you get the error? — Pᴇʜ, Mar 07 '18 at 14:28
You need to use loop to select each value in range B:B. Autofilter cant select all the values at a time — Deepak, Mar 07 '18 at 14:28
@PEH: Sorry my fault. It occours in Bereich.Autofilter Field:=21, Criteria1:="=" & IndustryCode — HPM, Mar 07 '18 at 14:29
@Deepak: Thanks for your advice. How would you code that in my case? :) — HPM, Mar 07 '18 at 14:33
@Deepak No loop needed, you just need to transform the matrix ;) making a 1 dimenional array out of a 2 dimensional one and use this as criteria (see my answer). — Pᴇʜ, Mar 07 '18 at 14:40
Note, that this solution can only work for non-numeric filter values because `Variant` is most likely to assume `String` values. So, if you are storing numbers as string into an array and then filter by that array then the filter will be a string filter (on a possibly numeric column). — Ralph, Mar 07 '18 at 14:52
@Ralph The `Variant` is only that the variable can contain an array but inside that array the items of that array still can be of type `Double` (when your read numbers from a range then `vartype(IndustryCode(1))` is Double if the first entry is a number even if `IndustryCode` is Variant). And that fact finally causes the issue that it fails filtering on true numbers. If they were strings then filtering would work. Excel is kinda odd with filtering and true numbers in VBA but [this answer](https://stackoverflow.com/a/42932732/3219613) illustrates it. — Pᴇʜ, Mar 07 '18 at 15:40

Pᴇʜ · Accepted Answer · 2018-03-07T15:54:18.867

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.

Excel VBA AutoFilter Codes

1 Answers1