| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
filter by more than 2 criteria
I'm applying the following filtering:
Worksheets("Sheet1").Select Selection.AutoFilter Field:=1, Criteria1:="=ERP", Operator:=xlOr, _ Criteria2:="=PM" ' Working well But, I need more criterias there such as: Selection.AutoFilter Field:=1, Criteria1:="=ERP", Operator:=xlOr, _ Criteria2:="=PM" ', Operator:=xlOr, Criteria3:="=HPW" , _ Operator:=xlOr, Criteria4:="=OJT", _ Operator:=xlOr, Criteria5:="=SBR", _ Operator:=xlOr, Criteria6:="=LD", _ Operator:=xlOr, Criteria7:="=CFT" How could I do it? Thanks |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
filter by more than 2 criteria
Hi Alex
You can use Advanced Filter to do this http://www.contextures.com/xladvfilter01.html Or insert a helper column and AutoFilter on that column =OR(A1={"A","B","C","D","E"}) EasyFilter have 5 options http://www.rondebruin.nl/easyfilter.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Alex" wrote in message ... I'm applying the following filtering: Worksheets("Sheet1").Select Selection.AutoFilter Field:=1, Criteria1:="=ERP", Operator:=xlOr, _ Criteria2:="=PM" ' Working well But, I need more criterias there such as: Selection.AutoFilter Field:=1, Criteria1:="=ERP", Operator:=xlOr, _ Criteria2:="=PM" ', Operator:=xlOr, Criteria3:="=HPW" , _ Operator:=xlOr, Criteria4:="=OJT", _ Operator:=xlOr, Criteria5:="=SBR", _ Operator:=xlOr, Criteria6:="=LD", _ Operator:=xlOr, Criteria7:="=CFT" How could I do it? Thanks |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
filter by more than 2 criteria
Alex wrote:
I'm applying the following filtering: Worksheets("Sheet1").Select Selection.AutoFilter Field:=1, Criteria1:="=ERP", Operator:=xlOr, _ Criteria2:="=PM" ' Working well But, I need more criterias there such as: Selection.AutoFilter Field:=1, Criteria1:="=ERP", Operator:=xlOr, _ Criteria2:="=PM" ', Operator:=xlOr, Criteria3:="=HPW" , _ Operator:=xlOr, Criteria4:="=OJT", _ Operator:=xlOr, Criteria5:="=SBR", _ Operator:=xlOr, Criteria6:="=LD", _ Operator:=xlOr, Criteria7:="=CFT" How could I do it? Thanks The way it's been done since the beginning of computer science is to sort on the least significant field first. Then do it again with the next least, and so forth until you're done. This is pretty much what any sorting software you'll find will do under the covers to handle multiple keys. Bill |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
filter by more than 2 criteria
Thanks a lot, Ron.
Could you please explain how to create this helper column. "Ron de Bruin" wrote: Hi Alex You can use Advanced Filter to do this http://www.contextures.com/xladvfilter01.html Or insert a helper column and AutoFilter on that column =OR(A1={"A","B","C","D","E"}) EasyFilter have 5 options http://www.rondebruin.nl/easyfilter.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Alex" wrote in message ... I'm applying the following filtering: Worksheets("Sheet1").Select Selection.AutoFilter Field:=1, Criteria1:="=ERP", Operator:=xlOr, _ Criteria2:="=PM" ' Working well But, I need more criterias there such as: Selection.AutoFilter Field:=1, Criteria1:="=ERP", Operator:=xlOr, _ Criteria2:="=PM" ', Operator:=xlOr, Criteria3:="=HPW" , _ Operator:=xlOr, Criteria4:="=OJT", _ Operator:=xlOr, Criteria5:="=SBR", _ Operator:=xlOr, Criteria6:="=LD", _ Operator:=xlOr, Criteria7:="=CFT" How could I do it? Thanks |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
filter by more than 2 criteria
Hi Alex
For example your data range is A1:G100 Row 1 are headers and you want to check A2:A100 for the Criteria In H1 enter header text In H2 copy this formula and copy it down to H100 =OR(A2={"ERP","PM","HPW","OJT","SBR"","LD","CFT"}) Now you can use AutoFilter on column H for True or False -- Regards Ron de Bruin http://www.rondebruin.nl "Alex" wrote in message ... Thanks a lot, Ron. Could you please explain how to create this helper column. "Ron de Bruin" wrote: Hi Alex You can use Advanced Filter to do this http://www.contextures.com/xladvfilter01.html Or insert a helper column and AutoFilter on that column =OR(A1={"A","B","C","D","E"}) EasyFilter have 5 options http://www.rondebruin.nl/easyfilter.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Alex" wrote in message ... I'm applying the following filtering: Worksheets("Sheet1").Select Selection.AutoFilter Field:=1, Criteria1:="=ERP", Operator:=xlOr, _ Criteria2:="=PM" ' Working well But, I need more criterias there such as: Selection.AutoFilter Field:=1, Criteria1:="=ERP", Operator:=xlOr, _ Criteria2:="=PM" ', Operator:=xlOr, Criteria3:="=HPW" , _ Operator:=xlOr, Criteria4:="=OJT", _ Operator:=xlOr, Criteria5:="=SBR", _ Operator:=xlOr, Criteria6:="=LD", _ Operator:=xlOr, Criteria7:="=CFT" How could I do it? Thanks |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
filter by more than 2 criteria
Hi Alex
I have a typo in the formula, this one is OK (one " to much in the other one) =OR(A2={"ERP","PM","HPW","OJT","SBR","LD","CFT"}) -- Regards Ron de Bruin http://www.rondebruin.nl "Ron de Bruin" wrote in message ... Hi Alex For example your data range is A1:G100 Row 1 are headers and you want to check A2:A100 for the Criteria In H1 enter header text In H2 copy this formula and copy it down to H100 =OR(A2={"ERP","PM","HPW","OJT","SBR"","LD","CFT"}) Now you can use AutoFilter on column H for True or False -- Regards Ron de Bruin http://www.rondebruin.nl "Alex" wrote in message ... Thanks a lot, Ron. Could you please explain how to create this helper column. "Ron de Bruin" wrote: Hi Alex You can use Advanced Filter to do this http://www.contextures.com/xladvfilter01.html Or insert a helper column and AutoFilter on that column =OR(A1={"A","B","C","D","E"}) EasyFilter have 5 options http://www.rondebruin.nl/easyfilter.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Alex" wrote in message ... I'm applying the following filtering: Worksheets("Sheet1").Select Selection.AutoFilter Field:=1, Criteria1:="=ERP", Operator:=xlOr, _ Criteria2:="=PM" ' Working well But, I need more criterias there such as: Selection.AutoFilter Field:=1, Criteria1:="=ERP", Operator:=xlOr, _ Criteria2:="=PM" ', Operator:=xlOr, Criteria3:="=HPW" , _ Operator:=xlOr, Criteria4:="=OJT", _ Operator:=xlOr, Criteria5:="=SBR", _ Operator:=xlOr, Criteria6:="=LD", _ Operator:=xlOr, Criteria7:="=CFT" How could I do it? Thanks |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
filter by more than 2 criteria
Thank you very much, Ron.
It should work but I just cannot handle with the formula. It says "The formula you typed contains an error". I've tried Ctrl+Shift+Enter but it doesn't help. "Ron de Bruin" wrote: Hi Alex For example your data range is A1:G100 Row 1 are headers and you want to check A2:A100 for the Criteria In H1 enter header text In H2 copy this formula and copy it down to H100 =OR(A2={"ERP","PM","HPW","OJT","SBR"","LD","CFT"}) Now you can use AutoFilter on column H for True or False -- Regards Ron de Bruin http://www.rondebruin.nl "Alex" wrote in message ... Thanks a lot, Ron. Could you please explain how to create this helper column. "Ron de Bruin" wrote: Hi Alex You can use Advanced Filter to do this http://www.contextures.com/xladvfilter01.html Or insert a helper column and AutoFilter on that column =OR(A1={"A","B","C","D","E"}) EasyFilter have 5 options http://www.rondebruin.nl/easyfilter.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Alex" wrote in message ... I'm applying the following filtering: Worksheets("Sheet1").Select Selection.AutoFilter Field:=1, Criteria1:="=ERP", Operator:=xlOr, _ Criteria2:="=PM" ' Working well But, I need more criterias there such as: Selection.AutoFilter Field:=1, Criteria1:="=ERP", Operator:=xlOr, _ Criteria2:="=PM" ', Operator:=xlOr, Criteria3:="=HPW" , _ Operator:=xlOr, Criteria4:="=OJT", _ Operator:=xlOr, Criteria5:="=SBR", _ Operator:=xlOr, Criteria6:="=LD", _ Operator:=xlOr, Criteria7:="=CFT" How could I do it? Thanks |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
filter by more than 2 criteria
Thank you very much again, Ron.
It's working just great. "Ron de Bruin" wrote: Hi Alex For example your data range is A1:G100 Row 1 are headers and you want to check A2:A100 for the Criteria In H1 enter header text In H2 copy this formula and copy it down to H100 =OR(A2={"ERP","PM","HPW","OJT","SBR"","LD","CFT"}) Now you can use AutoFilter on column H for True or False -- Regards Ron de Bruin http://www.rondebruin.nl "Alex" wrote in message ... Thanks a lot, Ron. Could you please explain how to create this helper column. "Ron de Bruin" wrote: Hi Alex You can use Advanced Filter to do this http://www.contextures.com/xladvfilter01.html Or insert a helper column and AutoFilter on that column =OR(A1={"A","B","C","D","E"}) EasyFilter have 5 options http://www.rondebruin.nl/easyfilter.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Alex" wrote in message ... I'm applying the following filtering: Worksheets("Sheet1").Select Selection.AutoFilter Field:=1, Criteria1:="=ERP", Operator:=xlOr, _ Criteria2:="=PM" ' Working well But, I need more criterias there such as: Selection.AutoFilter Field:=1, Criteria1:="=ERP", Operator:=xlOr, _ Criteria2:="=PM" ', Operator:=xlOr, Criteria3:="=HPW" , _ Operator:=xlOr, Criteria4:="=OJT", _ Operator:=xlOr, Criteria5:="=SBR", _ Operator:=xlOr, Criteria6:="=LD", _ Operator:=xlOr, Criteria7:="=CFT" How could I do it? Thanks |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
filter by more than 2 criteria
Hi Ron.........
I had a similar problem today, only I needed 20 or's on the Autofilter. I dug this solution of yours out of the archives and it worked swell........bet you never thought that when you offered it on 9/1/05 that you would actually solve a problem for someone 2 years later......... Thanks, Chuck, CABGx3 "Ron de Bruin" wrote: Hi Alex I have a typo in the formula, this one is OK (one " to much in the other one) =OR(A2={"ERP","PM","HPW","OJT","SBR","LD","CFT"}) -- Regards Ron de Bruin http://www.rondebruin.nl "Ron de Bruin" wrote in message ... Hi Alex For example your data range is A1:G100 Row 1 are headers and you want to check A2:A100 for the Criteria In H1 enter header text In H2 copy this formula and copy it down to H100 =OR(A2={"ERP","PM","HPW","OJT","SBR"","LD","CFT"}) Now you can use AutoFilter on column H for True or False -- Regards Ron de Bruin http://www.rondebruin.nl "Alex" wrote in message ... Thanks a lot, Ron. Could you please explain how to create this helper column. "Ron de Bruin" wrote: Hi Alex You can use Advanced Filter to do this http://www.contextures.com/xladvfilter01.html Or insert a helper column and AutoFilter on that column =OR(A1={"A","B","C","D","E"}) EasyFilter have 5 options http://www.rondebruin.nl/easyfilter.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Alex" wrote in message ... I'm applying the following filtering: Worksheets("Sheet1").Select Selection.AutoFilter Field:=1, Criteria1:="=ERP", Operator:=xlOr, _ Criteria2:="=PM" ' Working well But, I need more criterias there such as: Selection.AutoFilter Field:=1, Criteria1:="=ERP", Operator:=xlOr, _ Criteria2:="=PM" ', Operator:=xlOr, Criteria3:="=HPW" , _ Operator:=xlOr, Criteria4:="=OJT", _ Operator:=xlOr, Criteria5:="=SBR", _ Operator:=xlOr, Criteria6:="=LD", _ Operator:=xlOr, Criteria7:="=CFT" How could I do it? Thanks |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
filter by more than 2 criteria
Hi Chuck
Great to read that I did something good 2 years ago <vbg Google is your best friend -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "CLR" wrote in message ... Hi Ron......... I had a similar problem today, only I needed 20 or's on the Autofilter. I dug this solution of yours out of the archives and it worked swell........bet you never thought that when you offered it on 9/1/05 that you would actually solve a problem for someone 2 years later......... Thanks, Chuck, CABGx3 "Ron de Bruin" wrote: Hi Alex I have a typo in the formula, this one is OK (one " to much in the other one) =OR(A2={"ERP","PM","HPW","OJT","SBR","LD","CFT"}) -- Regards Ron de Bruin http://www.rondebruin.nl "Ron de Bruin" wrote in message ... Hi Alex For example your data range is A1:G100 Row 1 are headers and you want to check A2:A100 for the Criteria In H1 enter header text In H2 copy this formula and copy it down to H100 =OR(A2={"ERP","PM","HPW","OJT","SBR"","LD","CFT"}) Now you can use AutoFilter on column H for True or False -- Regards Ron de Bruin http://www.rondebruin.nl "Alex" wrote in message ... Thanks a lot, Ron. Could you please explain how to create this helper column. "Ron de Bruin" wrote: Hi Alex You can use Advanced Filter to do this http://www.contextures.com/xladvfilter01.html Or insert a helper column and AutoFilter on that column =OR(A1={"A","B","C","D","E"}) EasyFilter have 5 options http://www.rondebruin.nl/easyfilter.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Alex" wrote in message ... I'm applying the following filtering: Worksheets("Sheet1").Select Selection.AutoFilter Field:=1, Criteria1:="=ERP", Operator:=xlOr, _ Criteria2:="=PM" ' Working well But, I need more criterias there such as: Selection.AutoFilter Field:=1, Criteria1:="=ERP", Operator:=xlOr, _ Criteria2:="=PM" ', Operator:=xlOr, Criteria3:="=HPW" , _ Operator:=xlOr, Criteria4:="=OJT", _ Operator:=xlOr, Criteria5:="=SBR", _ Operator:=xlOr, Criteria6:="=LD", _ Operator:=xlOr, Criteria7:="=CFT" How could I do it? Thanks |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
filter by more than 2 criteria
You can also use a range with all values
See the example here (last autofilter example) http://www.rondebruin.nl/delete.htm#AutoFilter -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Hi Chuck Great to read that I did something good 2 years ago <vbg Google is your best friend -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "CLR" wrote in message ... Hi Ron......... I had a similar problem today, only I needed 20 or's on the Autofilter. I dug this solution of yours out of the archives and it worked swell........bet you never thought that when you offered it on 9/1/05 that you would actually solve a problem for someone 2 years later......... Thanks, Chuck, CABGx3 "Ron de Bruin" wrote: Hi Alex I have a typo in the formula, this one is OK (one " to much in the other one) =OR(A2={"ERP","PM","HPW","OJT","SBR","LD","CFT"}) -- Regards Ron de Bruin http://www.rondebruin.nl "Ron de Bruin" wrote in message ... Hi Alex For example your data range is A1:G100 Row 1 are headers and you want to check A2:A100 for the Criteria In H1 enter header text In H2 copy this formula and copy it down to H100 =OR(A2={"ERP","PM","HPW","OJT","SBR"","LD","CFT"}) Now you can use AutoFilter on column H for True or False -- Regards Ron de Bruin http://www.rondebruin.nl "Alex" wrote in message ... Thanks a lot, Ron. Could you please explain how to create this helper column. "Ron de Bruin" wrote: Hi Alex You can use Advanced Filter to do this http://www.contextures.com/xladvfilter01.html Or insert a helper column and AutoFilter on that column =OR(A1={"A","B","C","D","E"}) EasyFilter have 5 options http://www.rondebruin.nl/easyfilter.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Alex" wrote in message ... I'm applying the following filtering: Worksheets("Sheet1").Select Selection.AutoFilter Field:=1, Criteria1:="=ERP", Operator:=xlOr, _ Criteria2:="=PM" ' Working well But, I need more criterias there such as: Selection.AutoFilter Field:=1, Criteria1:="=ERP", Operator:=xlOr, _ Criteria2:="=PM" ', Operator:=xlOr, Criteria3:="=HPW" , _ Operator:=xlOr, Criteria4:="=OJT", _ Operator:=xlOr, Criteria5:="=SBR", _ Operator:=xlOr, Criteria6:="=LD", _ Operator:=xlOr, Criteria7:="=CFT" How could I do it? Thanks |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
filter by more than 2 criteria
Hi Ron..........
I love this idea, because it will allow the user to edit the list......but I can't seem to get it working........... For Each cell In CriteriaRng With Sheets("importeddata") 'Firstly, remove the AutoFilter .AutoFilterMode = False 'Apply the filter .Range("A1:A" & .Rows.Count).AutoFilter Field:=31, Criteria1:=cell.Value It fails in my XL97 on this line.......saying "Autofilter method of range class failed" Help please.... Vaya con Dios, Chuck, CABGx3 "Ron de Bruin" wrote: You can also use a range with all values See the example here (last autofilter example) http://www.rondebruin.nl/delete.htm#AutoFilter -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Hi Chuck Great to read that I did something good 2 years ago <vbg Google is your best friend -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "CLR" wrote in message ... Hi Ron......... I had a similar problem today, only I needed 20 or's on the Autofilter. I dug this solution of yours out of the archives and it worked swell........bet you never thought that when you offered it on 9/1/05 that you would actually solve a problem for someone 2 years later......... Thanks, Chuck, CABGx3 "Ron de Bruin" wrote: Hi Alex I have a typo in the formula, this one is OK (one " to much in the other one) =OR(A2={"ERP","PM","HPW","OJT","SBR","LD","CFT"}) -- Regards Ron de Bruin http://www.rondebruin.nl "Ron de Bruin" wrote in message ... Hi Alex For example your data range is A1:G100 Row 1 are headers and you want to check A2:A100 for the Criteria In H1 enter header text In H2 copy this formula and copy it down to H100 =OR(A2={"ERP","PM","HPW","OJT","SBR"","LD","CFT"}) Now you can use AutoFilter on column H for True or False -- Regards Ron de Bruin http://www.rondebruin.nl "Alex" wrote in message ... Thanks a lot, Ron. Could you please explain how to create this helper column. "Ron de Bruin" wrote: Hi Alex You can use Advanced Filter to do this http://www.contextures.com/xladvfilter01.html Or insert a helper column and AutoFilter on that column =OR(A1={"A","B","C","D","E"}) EasyFilter have 5 options http://www.rondebruin.nl/easyfilter.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Alex" wrote in message ... I'm applying the following filtering: Worksheets("Sheet1").Select Selection.AutoFilter Field:=1, Criteria1:="=ERP", Operator:=xlOr, _ Criteria2:="=PM" ' Working well But, I need more criterias there such as: Selection.AutoFilter Field:=1, Criteria1:="=ERP", Operator:=xlOr, _ Criteria2:="=PM" ', Operator:=xlOr, Criteria3:="=HPW" , _ Operator:=xlOr, Criteria4:="=OJT", _ Operator:=xlOr, Criteria5:="=SBR", _ Operator:=xlOr, Criteria6:="=LD", _ Operator:=xlOr, Criteria7:="=CFT" How could I do it? Thanks |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
filter by more than 2 criteria
This will only work if you want to delete/hide rows
Is that what you want to do ? If you only want to filter you can use Advancedfilter and use the range as your criteria Important: Use the same header as your filter column See Debra's site for more help http://www.contextures.com/xladvfilter01.html -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "CLR" wrote in message ... Hi Ron.......... I love this idea, because it will allow the user to edit the list......but I can't seem to get it working........... For Each cell In CriteriaRng With Sheets("importeddata") 'Firstly, remove the AutoFilter .AutoFilterMode = False 'Apply the filter .Range("A1:A" & .Rows.Count).AutoFilter Field:=31, Criteria1:=cell.Value It fails in my XL97 on this line.......saying "Autofilter method of range class failed" Help please.... Vaya con Dios, Chuck, CABGx3 "Ron de Bruin" wrote: You can also use a range with all values See the example here (last autofilter example) http://www.rondebruin.nl/delete.htm#AutoFilter -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Hi Chuck Great to read that I did something good 2 years ago <vbg Google is your best friend -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "CLR" wrote in message ... Hi Ron......... I had a similar problem today, only I needed 20 or's on the Autofilter. I dug this solution of yours out of the archives and it worked swell........bet you never thought that when you offered it on 9/1/05 that you would actually solve a problem for someone 2 years later......... Thanks, Chuck, CABGx3 "Ron de Bruin" wrote: Hi Alex I have a typo in the formula, this one is OK (one " to much in the other one) =OR(A2={"ERP","PM","HPW","OJT","SBR","LD","CFT"}) -- Regards Ron de Bruin http://www.rondebruin.nl "Ron de Bruin" wrote in message ... Hi Alex For example your data range is A1:G100 Row 1 are headers and you want to check A2:A100 for the Criteria In H1 enter header text In H2 copy this formula and copy it down to H100 =OR(A2={"ERP","PM","HPW","OJT","SBR"","LD","CFT"}) Now you can use AutoFilter on column H for True or False -- Regards Ron de Bruin http://www.rondebruin.nl "Alex" wrote in message ... Thanks a lot, Ron. Could you please explain how to create this helper column. "Ron de Bruin" wrote: Hi Alex You can use Advanced Filter to do this http://www.contextures.com/xladvfilter01.html Or insert a helper column and AutoFilter on that column =OR(A1={"A","B","C","D","E"}) EasyFilter have 5 options http://www.rondebruin.nl/easyfilter.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Alex" wrote in message ... I'm applying the following filtering: Worksheets("Sheet1").Select Selection.AutoFilter Field:=1, Criteria1:="=ERP", Operator:=xlOr, _ Criteria2:="=PM" ' Working well But, I need more criterias there such as: Selection.AutoFilter Field:=1, Criteria1:="=ERP", Operator:=xlOr, _ Criteria2:="=PM" ', Operator:=xlOr, Criteria3:="=HPW" , _ Operator:=xlOr, Criteria4:="=OJT", _ Operator:=xlOr, Criteria5:="=SBR", _ Operator:=xlOr, Criteria6:="=LD", _ Operator:=xlOr, Criteria7:="=CFT" How could I do it? Thanks |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
filter by more than 2 criteria
Ah so..........thank you very much kind Sir............
I think I'll just stick with your Plan-A, since it worked so easily. Vaya con Dios, Chuck, CABGx3 "Ron de Bruin" wrote: This will only work if you want to delete/hide rows Is that what you want to do ? If you only want to filter you can use Advancedfilter and use the range as your criteria Important: Use the same header as your filter column See Debra's site for more help http://www.contextures.com/xladvfilter01.html -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "CLR" wrote in message ... Hi Ron.......... I love this idea, because it will allow the user to edit the list......but I can't seem to get it working........... For Each cell In CriteriaRng With Sheets("importeddata") 'Firstly, remove the AutoFilter .AutoFilterMode = False 'Apply the filter .Range("A1:A" & .Rows.Count).AutoFilter Field:=31, Criteria1:=cell.Value It fails in my XL97 on this line.......saying "Autofilter method of range class failed" Help please.... Vaya con Dios, Chuck, CABGx3 "Ron de Bruin" wrote: You can also use a range with all values See the example here (last autofilter example) http://www.rondebruin.nl/delete.htm#AutoFilter -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Hi Chuck Great to read that I did something good 2 years ago <vbg Google is your best friend -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "CLR" wrote in message ... Hi Ron......... I had a similar problem today, only I needed 20 or's on the Autofilter. I dug this solution of yours out of the archives and it worked swell........bet you never thought that when you offered it on 9/1/05 that you would actually solve a problem for someone 2 years later......... Thanks, Chuck, CABGx3 "Ron de Bruin" wrote: Hi Alex I have a typo in the formula, this one is OK (one " to much in the other one) =OR(A2={"ERP","PM","HPW","OJT","SBR","LD","CFT"}) -- Regards Ron de Bruin http://www.rondebruin.nl "Ron de Bruin" wrote in message ... Hi Alex For example your data range is A1:G100 Row 1 are headers and you want to check A2:A100 for the Criteria In H1 enter header text In H2 copy this formula and copy it down to H100 =OR(A2={"ERP","PM","HPW","OJT","SBR"","LD","CFT"}) Now you can use AutoFilter on column H for True or False -- Regards Ron de Bruin http://www.rondebruin.nl "Alex" wrote in message ... Thanks a lot, Ron. Could you please explain how to create this helper column. "Ron de Bruin" wrote: Hi Alex You can use Advanced Filter to do this http://www.contextures.com/xladvfilter01.html Or insert a helper column and AutoFilter on that column =OR(A1={"A","B","C","D","E"}) EasyFilter have 5 options http://www.rondebruin.nl/easyfilter.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Alex" wrote in message ... I'm applying the following filtering: Worksheets("Sheet1").Select Selection.AutoFilter Field:=1, Criteria1:="=ERP", Operator:=xlOr, _ Criteria2:="=PM" ' Working well But, I need more criterias there such as: Selection.AutoFilter Field:=1, Criteria1:="=ERP", Operator:=xlOr, _ Criteria2:="=PM" ', Operator:=xlOr, Criteria3:="=HPW" , _ Operator:=xlOr, Criteria4:="=OJT", _ Operator:=xlOr, Criteria5:="=SBR", _ Operator:=xlOr, Criteria6:="=LD", _ Operator:=xlOr, Criteria7:="=CFT" How could I do it? Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Filter with criteria! | Excel Discussion (Misc queries) | |||
| Filter under and above certain criteria | Excel Discussion (Misc queries) | |||
| Filter criteria | Excel Discussion (Misc queries) | |||
| Filter Criteria | Excel Discussion (Misc queries) | |||
| "Criteria Range" in the "Data/Filter/Advanced Filter" to select Du | Excel Worksheet Functions | |||
Linear Mode
