Pedro said:
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
