Autofilter for more than one condition!!

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mellowe - 25 Jan 2006 12:42 GMT

Hi

Is it possible to have an autofilter on the header bar that can filter
for more than condition e.g
I have 600 rows of data, In Col A there are various groups names ;
Alpha, Beta, Charlie,Delta etc.

I would like a filter to show me all the entries for Alpha, Beta and
Delta at the same time.
So want to filter col A for not just one but say 3 or 4 conditions...
is this possible and can it be written in code?

Please help as cant seem to find a way to do this!! Thnx

Reply to this Message

Bob Phillips - 25 Jan 2006 13:16 GMT

One way

Sub Macro1()
Dim rng As Range

Columns("F:F").Insert
Set rng = Range("E1", Range("E1").End(xlDown))
Range("F2").FormulaR1C1 = _
"=ISNUMBER(MATCH(RC[-1],{""alpha"",""beta"",""delta""},0))"
Range("F2").AutoFill Destination:=Range("F2").Resize(rng.Rows.Count -
1), Type:=xlFillDefault
rng.Offset(0, 1).AutoFilter Field:=1, Criteria1:="TRUE"
End Sub

Signature

HTH

Bob Phillips

(remove nothere from email address if mailing direct)

> Hi
>
[quoted text clipped - 9 lines]
>
> Please help as cant seem to find a way to do this!! Thnx

Reply to this Message

mellowe - 25 Jan 2006 14:38 GMT

Thanks Bob for the quick response, but this doesnt seem to work. I used
Col A as rng. When it runs it collapses all of Col A as if has filtered
nothing and when you expand it to 'All' with the filter ,Col F has all
FALSE entries filled down. Any ideas?

Reply to this Message

Dave Peterson - 25 Jan 2006 15:10 GMT

Show all your data
then try putting Bob's formula in a cell manually:
=ISNUMBER(MATCH(A2,{"alpha","beta","delta"},0))
drag down the column

And then filter on this column

> Thanks Bob for the quick response, but this doesnt seem to work. I used
> Col A as rng. When it runs it collapses all of Col A as if has filtered
> nothing and when you expand it to 'All' with the filter ,Col F has all
> FALSE entries filled down. Any ideas?

Signature

Dave Peterson

Reply to this Message

mellowe - 26 Jan 2006 08:55 GMT

Thanks Dave! Worked for me ... just carried this out manually per your
suggestion and recorded it. Great.

Reply to this Message