VBA Macro Help in Excel

[tdrob28]

All,

I have a worksheet (attached). I am trying (and failing) to write VBA Macro that when it runs, it automatically filters a column containing names. I need it to show only the rows where certain names are found in the Column. I have been able to get it to show with single names, but for some reason, it wont return the row if there is more than one name in the column.

For Example:
Column A
Thomas Smith
Thomas Smith, Jane Doe
John Doe
John Doe, Jimmy Jack
Sara Stealth
Sara Stealth, Jenny Dim

When the macro runs, it returns just the rows that are bolded, but should return all rows....here is the Code

Sub Macro1()
    ActiveSheet.Range("$A$3:$K$143").AutoFilter Field:=6, Criteria1:=Array("Thomas Smith", _
        "Jane Doe", "John Doe", "Jimmy Jack", "Sara Stealth", "Jenny Dim"), Operator:=xlFilterValues
End Sub

[Squashman]

Did you try using Wildcards?

[tdrob28]

I did, I have to admit that I am not knowledgable of the formatting of wildcards, but whatever combination I tried to use it ended up not returning the row...for instance if i tried to use a wildcard for Thomas Smith, it would not return anything with Thomas Smith whereas before it would at least return rows with Thomas Smith being the only entry.

[Squashman]

Doesn't seem to like using Wildcards when there is more than 2 entries in the array.

If I chop down the array to this.

Code: [Select]

Sub NameFilter()
    ActiveSheet.Range("$A$1:$K$143").AutoFilter Field:=1, Criteria1:=Array("*Thomas Smith*", _
        "*John Doe*"), Operator:=xlFilterValues
End SubThe output is this.
Thomas Smith
Thomas Smith, Jane Doe
John Doe
John Doe, Jimmy Jack

If I add a 3rd entry to the arrary:

Code: [Select]

Sub NameFilter()
    ActiveSheet.Range("$A$1:$K$143").AutoFilter Field:=1, Criteria1:=Array("*Thomas Smith*", _
        "*John Doe*" , "Sarah Stealth"), Operator:=xlFilterValues
End SubNothing will output. 

You might have to try and build the array on the fly with the cells you actually want to display.  Physically look at each cell for your search terms and if it finds your search terms in the cell add that cells contents to the array.

Testing on Excel 2007

[soybean]

I haven't worked with macros for a long time but I simply had this thought about this scenario.  You say "When the macro runs, it returns just the rows that are bolded, but should return all rows ...".   As we can see, you have two names, i.e. two full (first and last name pairs), in some cells, separated by a comma.  Excel is returning the first name from the cell where that name matches a name contained in the macro.  So, the comma separator is preventing the names after a comma from being displayed.  That seems to be a key point that needs to be resolved.  I don't have the solution; just making an observation. 

[Squashman]

soybean,
The issue is that the whole Cell contents need to be matched.  When you turn on the filter option manually for a column you will see that the options for filter are both names combined with the comma because that is what is in the cell contents.  You can match with a Wildcard but then you are limited to two search terms in the arrary

It wouldn't be that hard to loop through the entire column and search for the Names in each cell and then put the cell contents into the Array.  I do something similar with VBscript but the syntax for VBA is a bit different so I am not sure how to code it for them.

[tdrob28]

Squashman, could you possibly givea start to it or post your vbscript...like I said I am very inexperienced in VBA and have exhausted my knowledge and google searches.

[Squashman]

Squashman, could you possibly givea start to it or post your vbscript...like I said I am very inexperienced in VBA and have exhausted my knowledge and google searches.

I am very new to vbscript as well and have never done much vba besides just recording a macro.
I have some code at work so I will have to get back to you on monday.