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

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

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

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