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

Hi Ron,

hypothetical always, i believe that data is as follows:

http://youtu.be/5lGbs02UcAA

Answer 2

I don't understand how your data and filterName's are set up on your worksheet or in your code, but it may be something for which the AdvancedFilter would be a better option.

Can you provide a screenshot or a link to your workbook that would explain the above in more detail?

Answer 3

Hi,

i think that, you can't handle this issue, using autofilter

Select a cell (in column E) that has specific names

and then run the code

also, you need a helper column ( eg column W)

Sub abcd()

'in row 1 headersIf MsgBox("select a specific group..", vbOKCancel) = vbCancel Then Exit Sub

Const Hcol As String = "W"   '<<< helper column, changeRange(Hcol & ":" & Hcol).ClearContents

Range("A:A").EntireRow.Hidden = False

Dim X As Range

Set X = Selection

Dim r As Long, i As Long, t As Long

r = Cells(Rows.Count, X.Column).End(xlUp).Row

v = Split(X.Value, ",")

For i = 0 To UBound(v)

Cells(i + 1, Hcol) = v(i)

Next

For Each rr In Range(Hcol & ":" & Hcol).SpecialCells(xlCellTypeConstants)

rr.Value = WorksheetFunction.Trim(rr)

Next rr

For i = 2 To r

t = 0

For Each rr In Range(Hcol & ":" & Hcol).SpecialCells(xlCellTypeConstants)

If Cells(i, X.Column).Value Like "*" & rr.Value & "*" Then t = t + 1

Next

If t = 0 Then Rows(i).Hidden = True

Next

End Sub

Answer 4

Thank you for the quick reply. I really appreciate your help.

I am creating arrays with names that I want to filter the column with. Sometimes the array has 2 names, sometimes it can have 12 names. There could be as many as 35 unique names in the column. "*,*" shows all cells with commas...which unfortunatly won't work for me as I want to show all the names in the array including cells that have just a single name (no comma) and only the names in the array (so if there is a set of names with a comma that doesn't have any of the names in the array it won't show). As the code is currently, when there are only 2 names in the array the filter works. When there are more than 2 names (3+) the filter doesn't work.

Answer 5

Hi,

names in column E.....

Tom

Ann

Peter, Tom, Mary

name1, name2, name3, name001

name10,name123, name99,name100,name321,....,lastName

.........

try this...

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

returns,

Peter, Tom, Mary

name1, name2, name3, name001

name10,name123, name99,name100,name321,....,lastName