Pass a string variable to an auto filter criteria array VBA

Question

Once again i am in trouble when trying to use array criteria in autofilters. Maybe someone will be able to solve it.

I have strings of criteria in an excel sheet in the following format: 12562,15215 (these are edited by users).

What i want to do is for a loop to take each cell containing these criteria and autofilter a certain field in another workbook using the cell contents as an array, then go to a next row of criteria.

The loop seems to be fine (i might be wrong) but when it comes to the autofilter, it does not find any cells. The string variable will not pass to the filter criteria array correctly (if i just hardcode the criteria to the filters - everything works).

Does my code contain some errors, or is the whole approach flawed?

Any assistance is appreciated.

Dim ExcRange As Range
Dim Col As Range
Dim Filter1 As Variant
With RawData.Worksheets(1)
.AutoFilterMode = False
End With
Dim Lastrow3 As Integer
Lastrow3 = RawData.Worksheets(1).Cells(Rows.Count, "O").End(xlUp).Row
Set ExcRange = RawData.Worksheets(1).Range("O11:O" & Lastrow3)

For Each Col In ExcRange

    If Not Col Is Nothing Then

    Filter1 = Split(Col.Value, ",")


    With NewBook.Worksheets(1)


        .Cells.AutoFilter Field:=1, Criteria1:=Array(Filter1), Operator:=xlFilterValues, Operator:=xlAnd

    End With

    Else
    End If

Next Col

I have also tried coding the autofilters this way:

.Cells.AutoFilter Field:=5, Criteria1:=Filter1, Operator:=xlFilterValues, Operator:=xlAnd

are there just number in ExcRange like this 12562 or there are number with quotation marks like this "12562". — Kazimierz Jawor
– Kazimierz Jawor, Commented Nov 22, 2013 at 15:31

Kazimierz Jawor · Accepted Answer · 2013-11-22 16:24:54Z

2

I think you need both- remove quotation marks and make an array as of each cell. You could achieve this by calling both Split and Replace function. Try to do it in this way:

Filter1 =Split(Replace(Col.Value, Chr(34), ""), ",")
Filter2 = Split(Replace(Col.Offset(0, 1).Value, Chr(34), ""), ",")
Filter3 = Split(Replace(Col.Offset(0, 2).Value, Chr(34), ""), ",")

and keep filtering in this way ...Criteria1:=Array(Filter1)...

It works if I made sample test.

answered Nov 22, 2013 at 16:24

Kazimierz Jawor

19.1k7 gold badges38 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mr_Oppenheimer Over a year ago

I can ask the users to enter the info without quotes. i have tried the split - but for that i need to change the Filter1 variable into Variant. if i do that it still does not work, the autofilter does not understand an array of numbers if it is a variant for some reason. I have simplified my code to one field filtering, and changed according to your advise - see above.

Kazimierz Jawor Over a year ago

@Mr_Oppenheimer, are you using English version of Office? Do you use COMMA (12,20) or DOT (12.20) to separate decimal numbers?

Mr_Oppenheimer Over a year ago

Yes it is an English version. decimal numbers separated by a comma. I was also wondering, can it have something to do with the formatting of the cells that are being filtered? The cells from which the criteria come are formatted as text

Collectives™ on Stack Overflow

Pass a string variable to an auto filter criteria array VBA

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related