VBA – 10. Auto Filters

autofilterDim r As Range

Set r = ThisWorkbook.Sheets("Sheet1").UsedRange


With r

'Filter the product group Global Business Services
 '.AutoFilter field:=5, Criteria1:="Global Business Services"


'Filter the product group Global Business Services or product group Corporate 
 .AutoFilter field:=5, Criteria1:="GlobalBusinessServices", Operator:=xlOr, Criteria2:="Corporate"
 .AutoFilter field:=6, Criteria1:="<20"

'Filter the Hours Spent less than 20 only
' .AutoFilter field:=6, Criteria1:="<20"
 
'Filter the hours between 20 and 30 only
' .AutoFilter field:=6, Criteria1:=">20", Operator:=xlAnd, Criteria2:="<=30"
 
 
'Filter the project for date received for Dec 2016 only
 Dim startDate As Date
 Dim endDate As Date
 
 startDate = DateSerial(2016, 1, 1)
 endDate = DateSerial(2016, 12, 31)
 
' .AutoFilter field:=4, Criteria1:=">=" & startDate, Operator:=xlAnd, Criteria2:="<=" & endDate


'Filter the project for Review and Analysis,Research and Memo Writing, Document Review task types
' .AutoFilter field:=3, Criteria1:=Array("Review and Summary", "Research and Memo Writing", "Document Review"), Operator:=xlFilterValues


'Filter the product group Global Business Services or product group Corporate  and then filter Hours spent for <20
 .AutoFilter field:=5, Criteria1:="GlobalBusinessServices", Operator:=xlOr, Criteria2:="Corporate"
 .AutoFilter field:=6, Criteria1:="<20"
End With

Leave a Reply