Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Filter
Hi,
Normally I use the following code to select a certain value in the filter Selection.AutoFilter Field:=12, Criteria1:="FIXED INCOME" but this only allows me to select only 2 criteria. What code should I write in order to select more than 2 criteria Regards, Pedro |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Filter
Auto filter only allows 2 criteria. Try advanced filtering, where your
criteria formula can be a lot more involved/creative! Bob Umlas Excel MVP "Pedro" wrote in message ... Hi, Normally I use the following code to select a certain value in the filter Selection.AutoFilter Field:=12, Criteria1:="FIXED INCOME" but this only allows me to select only 2 criteria. What code should I write in order to select more than 2 criteria Regards, Pedro |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Filter
Pedro wrote: Hi, Normally I use the following code to select a certain value in the filter Selection.AutoFilter Field:=12, Criteria1:="FIXED INCOME" but this only allows me to select only 2 criteria. What code should I write in order to select more than 2 criteria Regards, Pedro Hi You might be able to adapt this snippet. It uses a mixture of AutoFilter and Advanced Filter, depending on the number of Criteria. The code is part of a sub called by a form, where items to filter are selected from a listbox. 'Remove any filter present on the Range With myRange.Parent On Error Resume Next 'required if Advanced filter used ..ShowAllData On Error GoTo 0 ..AutoFilterMode = False 'Removes drop down arrows End With On Error GoTo 0 Select Case myCount 'count of things to filter Case 0 Exit Sub 'nothing happens and form is still visible 'Otherwise, filter the groups in "Group" column of myRange. 'Use AutoFilter if GroupCount is 1 or 2 Case 1 myRange.AutoFilter Field:=1, Criteria1:=CStr(listdata(1)) Case 2 myRange.AutoFilter Field:=1, Criteria1:=CStr(listdata(1)), Operator:=xlOr, Criteria2:=CStr(listdata(2)) Case Is 2 'Start by inserting a worksheet and creating a Criteria Range to put into AdvancedFilter 'My AddSheet sub first removes the sheet, so it is always a fresh sheet AddSheet "WorkSpaceSheet" 'now the active sheet With ActiveWorkbook.Worksheets("WorkSpaceSheet") ..Visible = False ..Cells(1, 1).Value = "Group" 'same as name on worksheet i = 1 'set a counter and put group items below "Group" For Each Item In listdata i = i + 1 ..Cells(i, 1).Value = Item Next Item Set GroupCriteria = .Cells(1, 1).CurrentRegion End With 'worksheet 'Filter the list myRange.Parent.Activate myRange.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=GroupCriteria End Select |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Filter
I use a helper column and put a formula in that cell that uses all the criteria
I need: =or(b2={"hi","there","what's","Happening"}) Make it as complex as you want. And then filter on that column. Pedro wrote: Hi, Normally I use the following code to select a certain value in the filter Selection.AutoFilter Field:=12, Criteria1:="FIXED INCOME" but this only allows me to select only 2 criteria. What code should I write in order to select more than 2 criteria Regards, Pedro -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel auto-filter does not filter certain columns | Excel Discussion (Misc queries) | |||
DataFilterAuto Filter in excel 2007? | New Users to Excel | |||
Excel 2007 Auto Filter Filter | Excel Discussion (Misc queries) | |||
Limit filter options in Auto Filter | Excel Discussion (Misc queries) | |||
Excel auto filter doesn't recoginize case - won't filter AA from A | Excel Discussion (Misc queries) |