Advanced Filter

[05track]

I am attempting to write a macro that incorporates an advanced filter
to search by ~100 criteria, across a list of ~200 columns x3000 rows.

Is there a way to implement this without running an advanced filter
with the same 100 criteria copied across 200 columns, one to match
each column in my list?

--Chris

 

[merjet]

I'm not clear on what you are asking. In any case, some columns can
have no criteria.

Hth,
Merjet

 

[05track]

I would like to use advanced filters (or something similar) to
essentially do this:

Here are my 100 criteria, display every row that
contains one of them in the spreadsheet.



Currently all I know how to do is:

Here are my 100 criteria, display every instance of them
in a specific column

and repeat that for every single column, which with 200
columns makes for a very nasty advanced filter



Thanks in advance!

--Chris

 

[Pete_UK]

Debra Dalgleish has some tips on using Advanced Filter here:

http://www.contextures.com/xladvfilter02.html

Hope this helps.

Pete

 

[merjet]

That's a little more specific. Alright, suppose A1:E10 is filled with
random numbers between 1 and 100, and you want to display a row only
if it contains one of the numbers in A1313. Then the folowing will
do that.

Sub MyFilter()
Dim rngCrit As Range
Dim c As Range
Dim bFound As Boolean
Dim iRow As Integer
Dim iCol As Integer
Dim ws As Worksheet

UndoMyFilter
Set ws = Sheets("Sheet1")
Set rngCrit = ws.Range("A1313")
For iRow = 1 To 10
bFound = False
For iCol = 1 To 5
For Each c In rngCrit
If ws.Cells(iRow, iCol) = c Then
bFound = True
Exit For
End If
Next c
Next iCol
If bFound = False Then ws.Rows(iRow).Hidden = True
Next iRow
End Sub

Sub UndoMyFilter()
Sheets("Sheet1").Rows("1:10").Hidden = False
End Sub

Hth,
Merjet

 

[05track]

That's perfect!!

Thanks for your help!

--Chris