title: Dynamic Criteria - Filtering Excel VBA
body: <p>I need to filter on the same criteria, but the values of the criteria are not always the same in the data that I receive, so they need to be dynamic.</p>
  
  <p>for eg.</p>
  
  <p>Dim crit1 as String
  crit1 = ?</p>
  
  <p>So that:</p>
  
  <p>Selection.AutoFilter Field:=4, Criteria1:=crit1</p>
  
  <p>here is my code</p>
  
  <pre><code>enter code herexDim TaskType, Status, Elapse As Long
  Dim Total, Completed As Variant
  Total = Array("COMPLETED", "ERROR", "KILLED")
  Completed = Array("COMPLETED")
  TaskType = WorksheetFunction.Match("tasktypeid", Rows("1:1"), 0)
  Status = WorksheetFunction.Match("status", Rows("1:1"), 0)
  Elapse = WorksheetFunction.Match("elapse", Rows("1:1"), 0)
  'Use Filter Criteria
  '100 Total
  With Sheets("Raw_Data")
  Set rnData = .UsedRange
  With rnData
  .AutoFilter field:=TaskType, Criteria1:="100"
  .AutoFilter field:=Status, Criteria1:=Total, Operator:=xlFilterValues
  .Select
          For Each rngarea In .SpecialCells(xlCellTypeVisible).Areas
          lcount = lcount + rngarea.Rows.Count
          Next
          a = lcount - 1
     End With
        End With
  '100 Completed
      With Sheets("Data")
      Set rnData = .UsedRange
      With rnData
      .AutoFilter field:=TaskType, Criteria1:="100"
      .AutoFilter field:=Status, Criteria1:=Completed, Operator:=xlFilterValues
      .Select
              For Each rngarea In .SpecialCells(xlCellTypeVisible).Areas
              lcount1 = lcount1 + rngarea.Rows.Count
              Next
              b = lcount1 - 1
         End With
            End With
  
  '101 Total
      With Sheets("Raw_Data")
      Set rnData = .UsedRange
      With rnData
      .AutoFilter field:=TaskType, Criteria1:="101"
      .AutoFilter field:=Status, Criteria1:=Total, Operator:=xlFilterValues
      .Select
              For Each rngarea In .SpecialCells(xlCellTypeVisible).Areas
              lcount2 = lcount2 + rngarea.Rows.Count
              Next
              c = lcount2 - 1
         End With
            End With
  '101 Completed
      With Sheets("Data")
      Set rnData = .UsedRange
      With rnData
      .AutoFilter field:=TaskType, Criteria1:="101"
      .AutoFilter field:=Status, Criteria1:=Completed, Operator:=xlFilterValues
      .Select
              For Each rngarea In .SpecialCells(xlCellTypeVisible).Areas
              lcount3 = lcount3 + rngarea.Rows.Count
              Next
              d = lcount3 - 1
         End With
            End With
  </code></pre>
  
  <p>In above code i have calculated criteria1:= as 100,101 statically but it should take dynamic value present in the filter. 
  Thanks in advance.</p>