Excel VBA auto filter with substring

Question

There are multiple rows in my excel with D column as: TDM - 02 Bundle Rehoming 5 NE, TDM - 02 Bundle Rehoming 23 NE, IP - 02 Bundle Rehoming 7 NE etc. Please note that the number of NEs are different at most of the places. I want to search a substring to auto filter in Excel VBA:

sht2.Select
    sht2.Activate
    If sht2.FilterMode Then
        sht2.ShowAllData
    End If

    sht2.Range("D1:D" & LastRow).AutoFilter Field:=4, Criteria1:=Array( _
        CStr("HYBRID ATM and IP - 02 Bundle Rehoming" & Chr(42)), _
        CStr("TDM - 02 Bundle Rehoming" & Chr(42)), _
        CStr("IP - 02 Bundle Rehoming" & Chr(42))), Operator:=xlFilterValues

However, This won't produce any results. There is no error generated but the results are empty rows.
I tried various other options such as "HYBRID ATM and IP - 02 Bundle Rehoming **" etc but without any success. Is there any way to auto-filter these substrings with having a variable ending.

The problem isn't your criteria strings (what's shown in your code is fine). The problem is that your Field = 4 even though you are only filtering a single column. The Field number is the number of columns from the left of the starting column. So for example, for columns C:X, field 1 is column C, field 2 is column D, etc. Change your Field to be the Field:=1 so that it references the left most column of your range, which is just column D. — tigeravatar
– tigeravatar, Commented Oct 26, 2017 at 18:13
Alternately, change your range to be "A1:D" so that field 4 references column D — tigeravatar
– tigeravatar, Commented Oct 26, 2017 at 18:14
It should be noted however that you can't use variables in the criteria array like you're trying to do unfortunately. For wildcard autofilters you're limited to only two using the xlOr operator. To workaround this you'd need to create a helper column with a formula that determines which rows should be shown based on your criteria, and then filter that column. — tigeravatar
– tigeravatar, Commented Oct 26, 2017 at 18:30
I am not agree with you here. You can use many strings in an array with auto filter and Operator:=xlFilterValues. The only condition is that these strings should match exactly with the value in the column. Also Range("D1:D" & LastRow).AutoFilter Field:=4 does not give me any issue with other auto filters in my code. The problem here is the regex inside an array as n8 defined below. — Heinreich213
– Heinreich213, Commented Oct 26, 2017 at 18:46

danieltakeshi · Accepted Answer · 2017-10-26 18:50:52Z

1

Regex

The Regex101 for the regex \d+(?=\s*NE)

The code for values on column D and writting the number on column E, so you can filter for column E:

Dim str As String
Dim objMatches As Object
lastrow = Cells(Rows.Count, "D").End(xlUp).Row
For i = 1 To lastrow
    str = Cells(i, "D")
    Set objRegExp = CreateObject("VBScript.RegExp") 'New regexp
    objRegExp.Pattern = "\d+(?=\s*NE)"
    objRegExp.Global = True
    Set objMatches = objRegExp.Execute(str)
    If objMatches.Count > 0 Then
        For Each m In objMatches
            Cells(i, "E") = m.Value
        Next
    End If
Next i

Remember to enable the reference

Result

Edit:

I misunderstood it, so here is a review of the Regex101 and code:

Dim str As String
Dim objMatches As Object
lastrow = Cells(Rows.Count, "D").End(xlUp).Row
For i = 1 To lastrow
    str = Cells(i, "D")
    Set objRegExp = CreateObject("VBScript.RegExp") 'New regexp
    objRegExp.Pattern = "\d+(?=\s*NE)"
    objRegExp.Global = True
    Set objMatches = objRegExp.Execute(str)
    If objMatches.Count > 0 Then
    k = 5
        For Each m In objMatches
           Cells(i, k) = m.Value
           k = k + 1
        Next
    End If
Next i

Result

So you can filter for the other columns just for numbers.

edited Oct 26, 2017 at 18:50

answered Oct 26, 2017 at 18:32

danieltakeshi

9399 silver badges42 bronze badges

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

1 Comment

Heinreich213 Over a year ago

Perfect, thanks for it. I am trying to filter these values and then put them in an array to reconstruct all unique string values. Then I can easily use auto-filter with this array.

n8. · Accepted Answer · 2017-10-26 18:08:07Z

0

You can provide a "regex" or an array of explicit values, but not an array of "regex". What you could do is create a loop that finds all the values you're looking for and provide those as an array.

answered Oct 26, 2017 at 18:08

n8.

1,7443 gold badges16 silver badges42 bronze badges

1 Comment

Heinreich213 Over a year ago

that's a good idea. Thanks for it. Let me check, if I can create a loop for it. Here only the number is changing and first I can find all unique values with different numbers.

Collectives™ on Stack Overflow

Excel VBA auto filter with substring

2 Answers 2

Regex

Result

Edit:

Result

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Regex

Result

Edit:

Result

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related