[VBA] Very noob question, how do I add a filter that checks if a column contains a word in VBA?

[mvfsullivan]

TLDR: Excel filters dont seem to be consistent enough, I need to use VBA to search the word "Urgent" in a column and filter only those rows so my thing can do the thing :D

I know this has been asked a billion times before but none of the answers make sense to me and the solutions shared are massive for what the other users specific questions are. This should be a relatively tiny few lines of code right? All of the ones I'm seeing have like 10-15 lines.

I have a table of a bunch of stuff that is auto sorted using the command below, and I need another function that lets me activate filters because for some reason Excel likes to forget what it needs to filter.

My table relies on pasting external excel info into a table, and the rest of the tables search for words in order to count up statistics based on whats pasted. I am then using a main "stat table" that basically references random bits of stats.

To auto sort the "top issues" stuff I use (there are like 20 lines of "Range" to sort other tables but I just included one to keep things short):

Sub SortDataWithHeader()
Range("B648:B671").Sort Key1:=Range("B648"), Order1:=xlDescending
End Sub

From what I have read, the below is able to filter but it doesnt do anything:

Sub FilterOn()
Range("D1").AutoFilter field:=1, Criteria1:="*Urgent*"
End Sub

The word I need to filter for is the word Urgent, and the actual table range is O777 to O1077 (the header is at O776)

Please help :D

You can give a quick answer but if you could break down what you did and why that would be amazing. Its cool if you dont want to though, I know a ton of you guys are running around answering a billion questions and I truly truly appreciate that you are all helping make the world more productive!!! Hats off to you all, seriously!! <3

 

[Tom Urtis]

Why are you filtering column D starting in cell D1 for the word Urgent that you expect to exist in column O in range O777:O1077.

 

[mvfsullivan]

I figured it out, I just used Macro recorder to see what I was doing wrong. It was just a table reference thing.

 

[mvfsullivan]

(In Mac OS at least: Tools > Macros > Record Macro, and then manually add a filter, stop the recording from the same area, and look at the new module in VBA editor)