Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.excel.misc,microsoft.public.excel
|
|||
|
|||
Advanced Filter
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 |
#2
Posted to microsoft.public.excel.programming,microsoft.public.excel.misc,microsoft.public.excel
|
|||
|
|||
Advanced Filter
I'm not clear on what you are asking. In any case, some columns can
have no criteria. Hth, Merjet |
#3
Posted to microsoft.public.excel.programming,microsoft.public.excel.misc,microsoft.public.excel
|
|||
|
|||
Advanced Filter
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 |
#4
Posted to microsoft.public.excel.programming,microsoft.public.excel.misc,microsoft.public.excel
|
|||
|
|||
Advanced Filter
Debra Dalgleish has some tips on using Advanced Filter he
http://www.contextures.com/xladvfilter02.html Hope this helps. Pete On May 2, 3:50 pm, wrote: 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 |
#5
Posted to microsoft.public.excel.programming,microsoft.public.excel.misc,microsoft.public.excel
|
|||
|
|||
Advanced Filter
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 A13:D13. 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("A13:D13") 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 |
#6
Posted to microsoft.public.excel.programming,microsoft.public.excel.misc,microsoft.public.excel
|
|||
|
|||
Advanced Filter
That's perfect!!
Thanks for your help! --Chris |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Advanced Filter - filter rows < | Excel Discussion (Misc queries) | |||
Why won't advanced filter return filter results? | Excel Worksheet Functions | |||
How do I use advanced filter to filter for blank cells? | Excel Discussion (Misc queries) | |||
"Criteria Range" in the "Data/Filter/Advanced Filter" to select Du | Excel Worksheet Functions | |||
advanced filter won't allow me to filter on bracketed text (-456.2 | Excel Discussion (Misc queries) |