Excel VBA - filtering using an array, spaces and commas

Question

I'm having problems with autofilter in Excel VBA. I need to filter a column of cells containing text to display instances of "IT", but not words containing the letters "it" - i.e. I want to filter for cells containing "Commercial, IT" but not "Commercial, Committee".

I'm using an array as per this question: Use autofilter on more than 2 criteria

It works when I use this code:

Worksheets("AssembledData").Range("A1").AutoFilter _
    field:=NewTeamCol, _
    Criteria1:=Array("IT", "IT, *"), _
    Operator:=xlFilterValues

But if I add a 3rd option to the array to catch cells where IT is at the end of the string Criteria1:=Array("IT", "IT, *", "* IT"), _, I get the same result as if I just had Criteria1:=Array("IT"), _

What am I doing wrong? New here, and new to VBA - done my best to search and not ask with this project, but stumped now! Thanks in advance.

You cannot use wildcards in a wide array of filter criteria with xlFilterValues. If you do, then only the first two criteria from the array are considered; just as if you were using Criteria1 and Criteria2 with Operator:=xlAnd. The only option is to loop through the cells and build your own array of criteria into an array then submit that into the .AutoFilter command. — user4039065
– user4039065, Commented May 10, 2015 at 18:02
Thanks @Jeeped - that matches what I'm seeing. Guess I'll have to go build an array properly (or bodge it with row visibility...). — daffdy
– daffdy, Commented May 10, 2015 at 18:22

Gary's Student · Accepted Answer · 2015-05-10 18:05:02Z

1

You can always "filter by hand:"

Sub qwerty()
    Dim N As Long, i As Long, v As String
    Dim r As Range
    N = Cells(Rows.Count, "A").End(xlUp).Row

    For i = 2 To N
        Set r = Cells(i, "A")
        v = r.Text
        If Right(v, 3) = " IT" Or Left(v, 4) = "IT, " Or v = "IT" Then
            r.EntireRow.Hidden = False
        Else
            r.EntireRow.Hidden = True
        End If
    Next i
End Sub

answered May 10, 2015 at 18:05

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

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

1 Comment

daffdy Over a year ago

Thanks! It works and I like 'handmade' code. Swapped the left and right bit for

If InStr(1, v, " IT", vbTextCompare) <> 0 Or InStr(1, v, "IT, ", vbTextCompare) <> 0 Or InStr(1, v, "IT ", vbTextCompare) <> 0 Or v = "IT" Then

to make sure all possible combinations of "IT" get picked up.

zedfoxus · Accepted Answer · 2015-05-10 17:53:54Z

0

Try omitting Operator:=xlFilterValues.

Since you are using wildcard characters along with xlFilterValues, Excel appears to compare literally IT, IT, * and * IT instead of using wildcard *.

For example, take this dataset:

   Name
1  john
2  nathjohn
3  test
4  abletest
5  testing
6  Commercial, IT
7  Commercial, Committee
8  IT, *

Using:

ActiveSheet.Range("$A$1:$A$15").AutoFilter _
        Field:=1, _
        Criteria1:=Array("IT", "IT, *", "* IT")

results in Commercial, IT because criteria's wildcards are correctly used with the exception of not choosing IT, *. Since that kind of value is not your immediate problem, we will conveniently skip over that corner case for now.

Using:

ActiveSheet.Range("$A$1:$A$15").AutoFilter _
    Field:=1, _
    Criteria1:=Array("IT", "IT, *", "* IT"), _
    Operator:=xlFilterValues

results in IT, * because xlFilterValues is using literal value of IT, *.

Give it a shot and see how things work.

answered May 10, 2015 at 17:53

zedfoxus

37.4k5 gold badges68 silver badges66 bronze badges

1 Comment

daffdy Over a year ago

Thanks. Tried it without Operator:=xlFilterValues but got no matches - wonder if I didn't apply your solution correctly? But @Jeeped's comment about wildcards suggests this might not be possible.

Collectives™ on Stack Overflow

Excel VBA - filtering using an array, spaces and commas

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related