Excel VBA auto filter with specific substring

Question

I have multiple row with different strings in which I want to filter data which are in this order

"DI then numbers" i.e DI07493A.

Column values are as such below:

01LICIN

05LICIN

AARHUSK

DI07493A

ABS16

DICOFDI

DI94193A

I am trying like this.

sheet1.Range("A1:A" & LastRow).AutoFilter Field:=4, Criteria1:= DI & #, Operator:=xlFilterValues

its not working. Is there a way to get this result?

Shouldn't it be filter data which are in this order "DI then numbers and letters" since your example (DI07493A) and others (DI94193A) have an `A` in the end, or do you not want DICOFDI but want any in this order "DI then numbers, and optionally ending with a letter". — user7393973, Aug 14 '18 at 09:10
yes Di and then number rest is optional. DICOFDI doesnt match my requirement. — Suman Kumar, Aug 14 '18 at 09:12
Not sure if that's possible with the filter (checking for a number in the middle of the value), you might need to instead use a loop to hide the rows which don't match your requirement. — user7393973, Aug 14 '18 at 09:24
I have huge data in sheet if I loop through each one it will consume a lot of time. Can you suggest something faster? — Suman Kumar, Aug 14 '18 at 09:34
Have a look at my answer. I think it might be what you need. — user7393973, Aug 14 '18 at 10:26
yup if we put those criteria in array would it work? I will get back to you buddy. — Suman Kumar, Aug 14 '18 at 10:41
It seems that unfortunately it's not possible to use an array instead of a range or criterias (I tried in VBA and in `Data > Sort & Filter > Advanced` without success). [Here's a post saying it doesn't work.](https://www.mrexcel.com/forum/excel-questions/324454-vba-advanced-filter-criteriarange.html#post1593948). You could [code them in and remove](https://www.mrexcel.com/forum/excel-questions/324454-vba-advanced-filter-criteriarange.html#post1593982). — user7393973, Aug 14 '18 at 10:48
The advanced filter is like the auto filter but with multiple criteria and the restriction that they need to be taken from a range. Excel doesn't support much with regex kind of operations unless you use "slower" code in a loop. — user7393973, Aug 14 '18 at 10:59
Thanks for the information, although on my system its not filtering as expected. All off the data got filtered and showing nothing. — Suman Kumar, Aug 14 '18 at 11:02

score 5 · Accepted Answer · answered Aug 14 '18 at 10:09

5

Try the following:

Have a column (lets say A1:A8) with a header (for example called Header) and then the values.

Then on column B, have the same header and the values from B2:B11 be DI0*, DI1*, DI2*, DI3*, DI4*, DI5*, DI6*, DI7*, DI8*, DI9*.

Then apply the AdvancedFilter with the code:

Range("A1:A8").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("B1:B11").

answered Aug 14 '18 at 10:09

user7393973

2,270
1
20
58

How come Criteria Range is from B1 as B1 is header? Anyways its not filtering as expected. Its giving me blank screen, all filtered. – Suman Kumar Aug 14 '18 at 11:00
@SumanKumar Did you used the same header for both the values and the filter criterias? If you used the correct ranges then it should work. Try the exact example I gave and if it works modify it bit by bit until you get it to the way you want it or if it doesn't work let me know more details of the way you tried it (code and images if possible). – user7393973 Aug 14 '18 at 11:03
@SumanKumar As you can see [here](https://www.ozgrid.com/forum/forum/help-forums/excel-general/59163-advanced-filter-without-headers?p=552306#post552306) and [here](https://stackoverflow.com/a/31776312), you need the headers. – user7393973 Aug 14 '18 at 11:06
Thanks a lot buddy this is what I was looking for. Because if I had gone through loop it would have been very lenthy. Thanks again for you great support and your insight. – Suman Kumar Aug 14 '18 at 11:06
Glad I was able to help. :) – user7393973 Aug 14 '18 at 11:07

Mentos · Answer 2 · 2018-08-14T09:53:54.110

2

Second Try with .Find

Sub find_my_stuff()

   Dim xFoundCell as Variant
   Dim Runner as Long
   Dim SomeString as String

   Set xFound Cell = Sheets("Sheet1").Range("A1:A" & LastRow).Find("DI", lookat:=xlPart_
   , MatchCase:=True
   Sheets("Sheet2").Range("A" & Runner) = xFoundCell.Address

   SomeString = Sheets("Sheet2").Range("A" & Runner)
   SomeString = Replace(SomeString, "A", "")
   SomeString = Replace(SomeString, "$", "")

   Sheets("Sheet2").Range("B" & Runner) = SomeString
   Sheets("Sheet2").Range("C" & Runner) = Sheets("sheet1").Cells(SomeString,1)
End Sub

As I said... super ugly, but maybe you can get an Idea what I tried to acomplish

edited Aug 14 '18 at 09:53

answered Aug 14 '18 at 09:38

Mentos

1,142
2
10
14

This wont do as it will get "DICOFDI" as well which I dont want. – Suman Kumar Aug 14 '18 at 09:40
A while ago I did something with the .Find option... but it is a little ugly to read... you know the .Find function? – Mentos Aug 14 '18 at 09:42
yes but how could that work? cuz it has something to do with Reg ex I suppose. I want to filter on the basis of pattern, which is First two character is "DI" and next one is number. – Suman Kumar Aug 14 '18 at 09:44
give me a second I'll get my code and show you an example of what could work for you – Mentos Aug 14 '18 at 09:46
You are right buddy, it is very ugly we can do this using pattern search which is more clean and efficient. I just need some to point me to right direction. – Suman Kumar Aug 14 '18 at 10:05

score 2 · Answer 3 · edited Feb 27 '20 at 02:40

2

You just need to add an asterix to the relevant part of your substring.

In your code, change this: Criteria1 := DI*

edited Feb 27 '20 at 02:40

Bioukh

1,888
1
16
27

answered Feb 26 '20 at 19:04

vbaJones

61
5

This just works if numbers AND letters are allowed after DI, but if I understood the question correctly, only numbers should be allowed after DI. – devbf Jul 29 '21 at 04:58

Michał Turczyn · Answer 4 · 2018-08-14T10:04:08.530

1

Try this code:

Sub Filter()
    Dim lastRow As Long, i As Long, ws As Worksheet
    Set ws = Worksheets("sheet_name")
    With ws
        lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        For i = 1 To lastRow
            'this conition checks if cell contains "DI" followed by a digit
            If Not Left(.Cells(i, 1).Value, 3) Like "DI[0-9]" Then
                .Rows(i).Hidden = True
            End If
        Next
    End With
End Sub

edited Aug 14 '18 at 10:04

answered Aug 14 '18 at 09:50

Michał Turczyn

32,028
14
47
69

can we apply it on filter criteria? Because I want these data in another sheet. if I do this one row at a time I think it will take longer? – Suman Kumar Aug 14 '18 at 10:02
@SumanKumar No, it's rather custom filtering. But you can specify in a code what sheet you want to filter. See updated answer. – Michał Turczyn Aug 14 '18 at 10:04

Excel VBA auto filter with specific substring

4 Answers4