How do I filter out specific text in Excel with a Macro?

Why is 22000 rows too much for an autofilter?

@Rory, because there are too many variants of Java. I will be here all day unchecking the ones I don't want. Unless you have a Filter that can give me lines with "Java 8 update 211" and above only. I need to Exclude anything that has Micro Focus or Eclipse on the lines as well.

When you say you "do not want these to be seen" do you mean that you want to delete them or do yu mean you want to hide them? In any case can you supply a sample workbook with some indications of the cells you want to hide/delete?

@Martin - they can either be filtered out or removed. All I want to see is the lines that have Java 8 update 211 and above every thing else can be filtered out or removed. 

"Java 8 update 211 and above" may be meaningful to you but without examples I can't be sure what it means.

@Martin

Column D has a few thing I need filtered out. You can wild card the Micro Focus* and Eclipse*

Micro Focus 

Eclipse

Java 8 Update 45  <-- this is how it appears in the column

Everything Above can be filter out.

All I need to see is  Java 8 Update 211 and above. I can't share the file because it has confidential information.

Below is how its formatted in the column

Java 8 Update 211

Java 8 Update 301

You might try AutoFilter on the results of this formula in an auxiliary column. It returns TRUE if both Java 8 and the last "word" is a number 211 or higher; otherwise, it returns FALSE.

=IFERROR(AND(SEARCH("Java 8",A2),--TEXTAFTER(A2," ",-1)>=211),FALSE)

Select allOpen in new window

And if you are running an older version of Excel (TEXTAFTER requires Microsoft 365), you can use:

=IFERROR(AND(SEARCH("Java 8",D2),--MID(D2,SEARCH("Update ",D2)+7,5)>=211),FALSE)

Select allOpen in new window

I just noticed that your question specified that the column to be searched is D. So I corrected this formula to use D2 instead of A2 in the previously suggested TEXTAFTER formula. 

@Byundt I am using Office 365 the latest version

Sub DeleteOld()
Dim lngLastRow As Long
Dim lngRow As Long
Dim intRelease As Integer

With Application
    .EnableEvents = False
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With

With ActiveSheet
    lngLastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    For lngRow = lngLastRow To 1 Step -1
        If IsNumeric(Mid(.Cells(lngRow, "D"), 15)) Then
            intRelease = CInt(Mid(.Cells(lngRow, "D"), 15))
            If intRelease < 211 Then
                .Cells(lngRow, "A").EntireRow.Delete
            End If
        End If
    Next
    
End With

With Application
    .EnableEvents = False
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With

End Sub

Select allOpen in new window

@Martin I assume I need to modify this code to what I want removed?

It will delete all rows where the number following "Java 8 Update" is less than 211.

Similar principle to some of above:

If you create a Pivot Table on your data you can get a list of unique values from the relevant column (column D). Copy that list out of the Pivot and you can then create a lookup table adding a value to each row stating whether a value is valid or not. With an additional column in your data you can then apply the relevant value to each row by doing a lookup to the list created, then filter on that column.

@Martin - I assume that I am doing something wrong, well,  I am definitely doing something wrong. I cannot get it to work.

@Rob - long time no see. Thanks for the suggestion. Let me try that out and get back to you.

Click the new button.
29247583a.xlsm

Edward,

If my formula is working, I can turn it into a macro if you like.

The best way to test is to use the UNIQUE function on column D to build a list of all the possible entries. Then test the formula against that.

Brad

@brad Thank you. I just got the file from the person, and it was a bit more complex than expected. See list of Java names below. I was just going by the info provided, now that I have the file, I see why nothing is working. What I need for anything that is update 211 and higher to be true. 

@Martin the macro worked on my file but the actual file that I received It did not get everything. If we can just concentrate on the word update and the number after it that would be great. If you guys need me to open another question, I have no problem doing so. Thanks for everyone help.

 

Lastly, I see Oracle had to name the JDK kits (11 and above) totally different than the previous versions.

What would be the basis for removing Java Auto Updater? The fact that it has no version number?

Should all the examples in your previous comment be removed?

@Martin no need to remove Java Auto updater.

@Brad I will try the new formula.

I don't think you can hide or delete all the rows you want to based solely on a single version number because I assume that Java 8 and Java SE are different products, each with it's own versioning,

Hello all. Sorry for the delay in response. I was travelling on business the last few days. I will check the comments and try all suggestions.

Martin this worked perfectly. To the rest of you, thank you all for your assistance. It was much appreciated.

While I thought of the concept independently, I think you should give Rob some credit for his multiple lists suggestion.

In any case you’re welcome and I’m glad I was able to help.

If you expand the “Full Biography" section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange Most Valuable Expert (MVE) 2015 and 2017
              Experts Exchange Distinguished Expert in Excel 2018 and 2021
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2021
              Experts Exchange Top Expert VBA 2018 to 2021

@Martin Thanks for the reminder. I normally do but it was crazy busy today and forgot to modify all other suggestions. 

Thanks again all.