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, Oct 26 '17 at 18:13
Alternately, change your range to be "A1:D" so that field 4 references column D — tigeravatar, Oct 26 '17 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, Oct 26 '17 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, Oct 26 '17 at 18:46
Ok, for wild card, I need to check if xlOr is a better option — Heinreich213, Oct 26 '17 at 18:57

danieltakeshi · Accepted Answer · 2017-10-26T18:50:52.917

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.

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. — Heinreich213, Oct 26 '17 at 18:44

score 0 · Answer 2 · answered Oct 26 '17 at 18:08

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 '17 at 18:08

n8.

1,732
3
16
38

1

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. – Heinreich213 Oct 26 '17 at 18:35

Excel VBA auto filter with substring

2 Answers2

Regex

Result

Edit:

Result

Linked