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

It is the auto filter that has the two criteria specification.  Advanced Filterstores the criteria in a criteria range, and the number of criteria of criteria, although not specified, is quite a bit larger than two.

Answer 2

Hi,

try and this approach, using Advanced Filter.

In cell C1 is drop down menu, data is in column A

from Column CA and to the right side, are helper columns (Criteria Range)

take a look this sample ...http://youtu.be/Gnidh30_DBw

XXXXXXXXXXXXXXXXXXXXXXXX

and the code..

Sub Adv_Filter()

On Error Resume Next

Const DataC = "A"    '<<< data in column A, change

'Note! column BZ (before first helper column), is always emptyConst HelpC As String = "CA"   '<<< helper columns from column CA and to the rightCells(1, HelpC).CurrentRegion.ClearContents

Dim x As Long, t As Long

Dim v As Variant

v = Split(Range("C1"), ",")   '<<< in cell C1 is drop down menu listt = Cells(1, HelpC).Column

Cells(1, HelpC).Resize(, UBound(v) + 1) = Cells(1, DataC)

For x = 0 To UBound(v)

Cells(2 + x, t).Value = WorksheetFunction.Trim(v(x))

Cells(2 + x, t).Value = CStr("*" & Cells(2 + x, t).Value & "*")

t = t + 1

Next

Dim r As Long

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

Range(DataC & "1:" & DataC & r).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _

Cells(1, HelpC).CurrentRegion, Unique:=False

MsgBox "next.."

Range(DataC & "1:" & DataC & r).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= Range("A1")

End Sub

(make a copy, before you run the code)

Note

if you want to see Criteria range

in the sample above,

set as first helper column, E column.

(try my sample, in a new workbook)

Answer 3

Hi Ron,

I realized the reason I was running into a problem was becuase I was using the Adavanced Filter and there seems to be a cap in excel of two criteria. I was trying to figure out a way around this cap.

Thanks for your response.

Answer 4

Hi TasosK,

Yes this is exactly what I am trying to do...basically.

Thank you!

Answer 5

I really like they way you've implemented that solution!

And if that is what the OP wants, it would be ideal.  But if what he wants is to return, for example

• a single name that exists, in the list, only in a group
• multiple names that exist separately in separate groups or by themselves

it might be better to return the name(s) from a separate list that allows multi-select rather than from the data itself.  I think a similar approach to yours could be done with the Advanced Filter -- whether it would be significantly faster or not probably depends on the size of the database.