Using vbscript with filter - doesn't show information when there are 3 or more people?

Question

I hope I can explain this in a way that is understandable. I am new to vbscript in excel and trying to filter a column by using the .AutoFilter in vbscript.

Here is what the code looks like:

ActiveSheet.Range("$A:$H").AutoFilter Field:=5, Criteria1:=filterName, Operator:= _

        xlFilterValues

filterName is the name of the person that I want to filter by.

This code seems to work with:

1. a single name - Person1Name

2. an array of names - Person1Name, Person2Name, etc. (works with up to 12 names so far, haven't had to go higher than that)
3. a single name with =*Person1Name* (to filter all instances of the name)
4. with two people in an array each with their names as =*Person1Name*, =*Person2Name*

As soon as there is an array with 3 names in the array filterName each represented as =*PersonName*  then nothing shows up in the sheet. The creation of the array doesn't change from 2 people to 3 people.

Hope this makes sense. Any suggestions as to what is happening or how I can fix this?

Thank you for your help!

Answer 1

I did a different approach.  This does not use the Advanced Filter but allows you to pick any of several names from the list, and hides those rows that don't contain any of the names.

I did use the layout similar to that suggested by TasosK in his video.  As well as some code from Chip Pearson to hide the UserForm box.

This does not require you to make up any name list -- it is written for the names to be in Column "A" starting in A2 (that is easily changed in the code.

When you press the "Select Names" button, a list of the names in the list is generated, and presented in a form, for selection.  You can select as many as you want (and if there are more names than will fit, the list is scrollable.  If you then hit "Filter", the rows without the selected names will be hidden.  You can go back and modify the list; or you can re-set it to no names by hitting the Reset button, either on the form or on the worksheet.

And after pressing "filter" on the form:

There is a lot of code in the workbook, so I've uploaded a copy to Skydrive here.

NameFilter workbook

It may be more than you need, or not what you need.  Or there may be inappropriate features.  Post back with any questions.